hrs, mins and seconds do not appear with to_char

From: Peter Koukoulis <pkoukoulis(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: hrs, mins and seconds do not appear with to_char
Date: 2017-08-27 20:23:14
Message-ID: CABpxA9gSciGSLXLjX_6o2By1EjE1-tPUYm0Mts728pZPwC_a=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I am unsure as to why the hrs, mins and seconds do not appear for a date
column. I am using PostgreSQL 9.6.3 on Linux.
When performing the exact same queries in Oracle, I get the full date
formatted to "yyyymmddhh24miss", but cannot get the same for PostgreSQL,
for example:

ft_node=# create table t3 (a numeric, b varchar(10), d date);
CREATE TABLE
ft_node=# insert into t3 values (1,'Yellow',
to_date('20170827121212','yyyymmddhh24miss'));
INSERT 0 1
ft_node=# insert into t3 values (2,'Red',
to_date('20170827121213','yyyymmddhh24miss'));
INSERT 0 1
ft_node=# select a,b,to_char(d,'YYYYMMDDHH24MISS') as d from t3;
a | b | d
---+--------+----------------
1 | Yellow | 20170827000000
2 | Red | 20170827000000
(2 rows)

In Oracle I get the following:
SQL> create table t3 (a numeric, b varchar(10), d date);

Table created.

SQL> insert into t3 values (1,'Yellow',
to_date('20170827121212','yyyymmddhh24miss'));

1 row created.

SQL> insert into t3 values (2,'Red',
to_date('20170827121213','yyyymmddhh24miss'));

1 row created.

SQL> commit;

Commit complete.

SQL> select a,b,to_char(d,'yyyymmddhh24miss') as d from t3;

A B D
---------- ---------- --------------
1 Yellow 20170827121212
2 Red 20170827121213

As you can see, the hrs, mins and seconds appear as was inserted in Oracle,
but not for PostgreSQL. Any suggestions?

Thanks
P

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dmitry Igrishin 2017-08-27 20:32:59 Re: Using the dollar sign as a prefix for named parameter of prepared statement.
Previous Message Christoph Moench-Tegeder 2017-08-27 20:22:25 Re: Log shipping in v8.4.7