hrs, mins and seconds do not appear with to_char

Started by Peter Koukoulisover 8 years ago3 messagesgeneral
Jump to latest
#1Peter Koukoulis
pkoukoulis@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Peter Koukoulis (#1)
Re: hrs, mins and seconds do not appear with to_char

Peter Koukoulis <pkoukoulis@gmail.com> writes:

I am unsure as to why the hrs, mins and seconds do not appear for a date
column.

Uh, because it's a date.

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:

Oracle has a nonstandard notion of what "date" means, I believe. You
probably want to use type "timestamp", and the to_timestamp() function,
in PG if you want behavior similar to what Oracle is doing.

https://www.postgresql.org/docs/current/static/datatype-datetime.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Peter Koukoulis
pkoukoulis@gmail.com
In reply to: Tom Lane (#2)
Re: hrs, mins and seconds do not appear with to_char

thanks. didn't realise they were different. I discovered the difference
when using a MD5 comparison between the 2 databases in a C++ utility.
All values were matching apart from dates.

Cheers
P

On Sun, 27 Aug 2017 at 21:35 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Peter Koukoulis <pkoukoulis@gmail.com> writes:

I am unsure as to why the hrs, mins and seconds do not appear for a date
column.

Uh, because it's a date.

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:

Oracle has a nonstandard notion of what "date" means, I believe. You
probably want to use type "timestamp", and the to_timestamp() function,
in PG if you want behavior similar to what Oracle is doing.

https://www.postgresql.org/docs/current/static/datatype-datetime.html

regards, tom lane