You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Khurram Faraaz <kf...@maprtech.com> on 2016/05/19 10:53:24 UTC
date_trunc function results on Drill vs Postgress
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
Re: date_trunc function results on Drill vs Postgress
Posted by Venki Korukanti <ve...@gmail.com>.
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 <
aengelbrecht@maprtech.com> 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 <zf...@maprtech.com> 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 <kf...@maprtech.com>
> > 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
> >>
>
>
Re: date_trunc function results on Drill vs Postgress
Posted by Andries Engelbrecht <ae...@maprtech.com>.
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 <zf...@maprtech.com> 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 <kf...@maprtech.com>
> 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
>>
Re: date_trunc function results on Drill vs Postgress
Posted by Zelaine Fong <zf...@maprtech.com>.
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 <kf...@maprtech.com>
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
>