In Postgres: output type of to_timestamp is TIMESTAMP WITH TIME ZONE and
date_trunc return the same type.
If you pass a timestamp without timezone as input, date_trunc returns a
timestamp without time zone.
In Drill, we don't have timestamp with time zone.
postgres=# select pg_typeof(date_trunc('hour', TIMESTAMP '2001-02-16
20:38:40'));
pg_typeof
-----------------------------
timestamp without time zone
(1 row)
postgres=# select date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
date_trunc
---------------------
2001-02-16 20:00:00
(1 row)
postgres=# select pg_typeof(to_timestamp(292278993));
pg_typeof
--------------------------
timestamp with time zone
(1 row)
postgres=# select to_timestamp(292278993);
to_timestamp
------------------------
1979-04-06 12:36:33-08
(1 row)
postgres=# SELECT pg_typeof(date_trunc('year', to_timestamp(292278993)));
pg_typeof
--------------------------
timestamp with time zone
(1 row)
postgres=# SELECT date_trunc('year', to_timestamp(292278993));
date_trunc
------------------------
1979-01-01 00:00:00-08
(1 row)
On Thu, May 19, 2016 at 9:25 AM, Andries Engelbrecht <
[email protected]> wrote:
> Could the -08 refer to timezone PST is UTC-8?
>
> Seems to be correct for both if the last identifier refers to timezone and
> the test system is set to PST.
>
>
> http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
> <
> http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
> >
>
>
>
> --Andries
>
>
> > On May 19, 2016, at 8:13 AM, Zelaine Fong <[email protected]> wrote:
> >
> > The Drill result seems more correct to me. Not sure why the "08" is
> there
> > in the case of Postgres.
> >
> > -- Zelaine
> >
> > On Thu, May 19, 2016 at 3:53 AM, Khurram Faraaz <[email protected]>
> > wrote:
> >
> >> There is a difference in result seen on Postgres vs Drill for a query
> that
> >> uses date_trunc function. Can someone please confirm which one is the
> >> correct expected behavior ?
> >>
> >> From Drill 1.7.0
> >>
> >> 0: jdbc:drill:schema=dfs.tmp> SELECT date_trunc('year',
> >> to_timestamp(292278993)) from sys.version;
> >> +------------------------+
> >> | EXPR$0 |
> >> +------------------------+
> >> | 1979-01-01 00:00:00.0 |
> >> +------------------------+
> >> 1 row selected (0.288 seconds)
> >>
> >> From Postgres 9.3 (note there is *00-08* towards the end in the result)
> >>
> >> postgres=# SELECT date_trunc('year', to_timestamp(292278993));
> >> date_trunc
> >> ------------------------
> >> 1979-01-01 00:00:00-08
> >> (1 row)
> >>
> >> Thanks,
> >> Khurram
> >>
>
>
