You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Divya Gehlot <di...@gmail.com> on 2018/07/03 15:21:39 UTC
difference between cast as timestamp and to_timestamp
Hi,
Below gives me different values
Query 1:
select CAST(1530601200049 AS TimeStamp) from (values(1));
EXPR$0
2018-07-03T07:00:00.049-05:00
Query 2:
select TO_TIMESTAMP(1530601200049) from (values(1));
Apache Drill
50472-10-26T11:00:49.000-05:00
Query 3 :
select TO_TIMESTAMP(1530601200049,'YYYY-MM-dd HH:mm:ss') from (values(1));
Error :
Apache Drill
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
IllegalArgumentException: Invalid format: "1530601200049" is malformed at
"0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on <node1>:31010]
Why get I different values?
Thanks,
Divya
Re: difference between cast as timestamp and to_timestamp
Posted by Chris Cunningham <cu...@gmail.com>.
Hi Divya,
TO_TIMESTAMP takes varchar (string) input to convert to timestamps, not
numbers.
https://drill.apache.org/docs/data-type-conversion/#to_timestamp
Thanks,
Chris
On Wed, Jul 4, 2018 at 2:57 AM Divya Gehlot <di...@gmail.com> wrote:
> Hi ,
> But Why I get the timestamp in below format
>
> 2018-07-03T07:00:00.049-05:00
>
> while you get in 2018-07-03 00:00:00.0
> do I need to make any config changes for it ?
>
> Why the Query 3 still gives me error
> select TO_TIMESTAMP(1530601200049/1000,'YYYY-MM-dd HH:mm:ss') from
> (values(1));
> Apache Drill
> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> IllegalArgumentException: Invalid format: "1530601200" is malformed at "0"
> [Error Id: ca3236d5-e99b-4a72-a915-8d4fe64afee6 on <node1>:31010]
>
> Doesn't it works this way ?
>
> Thanks,
> Divya
>
> On Wed, 4 Jul 2018 at 02:30, Arjun kr <ar...@outlook.com> wrote:
>
> > TO_TIMESTAMP function accepts epoch timestamp in seconds. Whereas cast to
> > Timestamp seems to be expecting value in milliseconds.
> >
> >
> >
> > 0: jdbc:drill:> select TO_TIMESTAMP(1530601200049/1000) from (values(1));
> > +------------------------+
> > | EXPR$0 |
> > +------------------------+
> > | 2018-07-03 00:00:00.0 |
> > +------------------------+
> > 1 row selected (0.297 seconds)
> > 0: jdbc:drill:>
> >
> > Thanks,
> >
> > Arjun
> >
> > ________________________________
> > From: Divya Gehlot <di...@gmail.com>
> > Sent: Tuesday, July 3, 2018 8:51 PM
> > To: user@drill.apache.org
> > Subject: difference between cast as timestamp and to_timestamp
> >
> > Hi,
> > Below gives me different values
> > Query 1:
> > select CAST(1530601200049 AS TimeStamp) from (values(1));
> > EXPR$0
> > 2018-07-03T07:00:00.049-05:00
> >
> > Query 2:
> > select TO_TIMESTAMP(1530601200049) from (values(1));
> > Apache Drill
> > 50472-10-26T11:00:49.000-05:00
> >
> > Query 3 :
> > select TO_TIMESTAMP(1530601200049,'YYYY-MM-dd HH:mm:ss') from
> (values(1));
> > Error :
> > Apache Drill
> > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> > IllegalArgumentException: Invalid format: "1530601200049" is malformed at
> > "0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on <node1>:31010]
> >
> > Why get I different values?
> >
> > Thanks,
> > Divya
> >
>
Re: difference between cast as timestamp and to_timestamp
Posted by Divya Gehlot <di...@gmail.com>.
Hi ,
But Why I get the timestamp in below format
2018-07-03T07:00:00.049-05:00
while you get in 2018-07-03 00:00:00.0
do I need to make any config changes for it ?
Why the Query 3 still gives me error
select TO_TIMESTAMP(1530601200049/1000,'YYYY-MM-dd HH:mm:ss') from
(values(1));
Apache Drill
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
IllegalArgumentException: Invalid format: "1530601200" is malformed at "0"
[Error Id: ca3236d5-e99b-4a72-a915-8d4fe64afee6 on <node1>:31010]
Doesn't it works this way ?
Thanks,
Divya
On Wed, 4 Jul 2018 at 02:30, Arjun kr <ar...@outlook.com> wrote:
> TO_TIMESTAMP function accepts epoch timestamp in seconds. Whereas cast to
> Timestamp seems to be expecting value in milliseconds.
>
>
>
> 0: jdbc:drill:> select TO_TIMESTAMP(1530601200049/1000) from (values(1));
> +------------------------+
> | EXPR$0 |
> +------------------------+
> | 2018-07-03 00:00:00.0 |
> +------------------------+
> 1 row selected (0.297 seconds)
> 0: jdbc:drill:>
>
> Thanks,
>
> Arjun
>
> ________________________________
> From: Divya Gehlot <di...@gmail.com>
> Sent: Tuesday, July 3, 2018 8:51 PM
> To: user@drill.apache.org
> Subject: difference between cast as timestamp and to_timestamp
>
> Hi,
> Below gives me different values
> Query 1:
> select CAST(1530601200049 AS TimeStamp) from (values(1));
> EXPR$0
> 2018-07-03T07:00:00.049-05:00
>
> Query 2:
> select TO_TIMESTAMP(1530601200049) from (values(1));
> Apache Drill
> 50472-10-26T11:00:49.000-05:00
>
> Query 3 :
> select TO_TIMESTAMP(1530601200049,'YYYY-MM-dd HH:mm:ss') from (values(1));
> Error :
> Apache Drill
> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> IllegalArgumentException: Invalid format: "1530601200049" is malformed at
> "0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on <node1>:31010]
>
> Why get I different values?
>
> Thanks,
> Divya
>
Re: difference between cast as timestamp and to_timestamp
Posted by Arjun kr <ar...@outlook.com>.
TO_TIMESTAMP function accepts epoch timestamp in seconds. Whereas cast to Timestamp seems to be expecting value in milliseconds.
0: jdbc:drill:> select TO_TIMESTAMP(1530601200049/1000) from (values(1));
+------------------------+
| EXPR$0 |
+------------------------+
| 2018-07-03 00:00:00.0 |
+------------------------+
1 row selected (0.297 seconds)
0: jdbc:drill:>
Thanks,
Arjun
________________________________
From: Divya Gehlot <di...@gmail.com>
Sent: Tuesday, July 3, 2018 8:51 PM
To: user@drill.apache.org
Subject: difference between cast as timestamp and to_timestamp
Hi,
Below gives me different values
Query 1:
select CAST(1530601200049 AS TimeStamp) from (values(1));
EXPR$0
2018-07-03T07:00:00.049-05:00
Query 2:
select TO_TIMESTAMP(1530601200049) from (values(1));
Apache Drill
50472-10-26T11:00:49.000-05:00
Query 3 :
select TO_TIMESTAMP(1530601200049,'YYYY-MM-dd HH:mm:ss') from (values(1));
Error :
Apache Drill
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
IllegalArgumentException: Invalid format: "1530601200049" is malformed at
"0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on <node1>:31010]
Why get I different values?
Thanks,
Divya