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/01/15 07:09:04 UTC

convert epoch time stamp to timestamp

Hi ,
One of field in my data file is  in epoch time stamp .
The values would be similar like 1515545336591

How can I convert this to 'YYYY-MM-DD HH:mm:ss' format .

Thanks,
Divya

Re: convert epoch time stamp to timestamp

Posted by Divya Gehlot <di...@gmail.com>.

Thanks Arjun
After posting in forum I also tried the same solution what you have posted
and it worked :)

Thanks for the help !

On 15 January 2018 at 15:31, Arjun kr <ar...@outlook.com> wrote:

> Looks like you are passing epoch timestamp value in milliseconds instead
> of seconds. You can divide by 1000 or remove last three digits to see if
> you are getting the desired result.
>
>
> # Divide by 1000
>
>
> SELECT TO_TIMESTAMP(1515545336591/1000) FROM (VALUES(1));
> +------------------------+
> |         EXPR$0         |
> +------------------------+
> | 2018-01-09 19:48:56.0  |
> +------------------------+
> 1 row selected (0.2 seconds)
>
> # last 3 digits removed.
>
> SELECT TO_TIMESTAMP(1515545336) FROM (VALUES(1));
> +------------------------+
> |         EXPR$0         |
> +------------------------+
> | 2018-01-09 19:48:56.0  |
> +------------------------+
> 1 row selected (0.461 seconds)
>
>
> Thanks,
>
> Arjun
>
>
> ________________________________
> From: Divya Gehlot <di...@gmail.com>
> Sent: Monday, January 15, 2018 12:46 PM
> To: user@drill.apache.org
> Subject: Re: convert epoch time stamp to timestamp
>
> When I
> SELECT
> CAST(FROM_UNIXTIME(t.`timestamp`) AS TIMESTAMP) AS `timestamp`
> FROM
> path/data/file  t limit 10 ;
>
> 49995-02-13T19:51:48.000Z
> 49995-04-22T15:47:05.000Z
> 49996-09-06T03:21:25.000Z
> 49997-03-05T01:20:19.000Z
>
> I get these values .
>
> Thanks,
> Divya
>
> On 15 January 2018 at 15:09, Divya Gehlot <di...@gmail.com> wrote:
>
> > Hi ,
> > One of field in my data file is  in epoch time stamp .
> > The values would be similar like 1515545336591
> >
> > How can I convert this to 'YYYY-MM-DD HH:mm:ss' format .
> >
> > Thanks,
> > Divya
> >
>

Re: convert epoch time stamp to timestamp

Posted by Arjun kr <ar...@outlook.com>.

Looks like you are passing epoch timestamp value in milliseconds instead of seconds. You can divide by 1000 or remove last three digits to see if you are getting the desired result.


# Divide by 1000


SELECT TO_TIMESTAMP(1515545336591/1000) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2018-01-09 19:48:56.0  |
+------------------------+
1 row selected (0.2 seconds)

# last 3 digits removed.

SELECT TO_TIMESTAMP(1515545336) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2018-01-09 19:48:56.0  |
+------------------------+
1 row selected (0.461 seconds)


Thanks,

Arjun


________________________________
From: Divya Gehlot <di...@gmail.com>
Sent: Monday, January 15, 2018 12:46 PM
To: user@drill.apache.org
Subject: Re: convert epoch time stamp to timestamp

When I
SELECT
CAST(FROM_UNIXTIME(t.`timestamp`) AS TIMESTAMP) AS `timestamp`
FROM
path/data/file  t limit 10 ;

49995-02-13T19:51:48.000Z
49995-04-22T15:47:05.000Z
49996-09-06T03:21:25.000Z
49997-03-05T01:20:19.000Z

I get these values .

Thanks,
Divya

On 15 January 2018 at 15:09, Divya Gehlot <di...@gmail.com> wrote:

> Hi ,
> One of field in my data file is  in epoch time stamp .
> The values would be similar like 1515545336591
>
> How can I convert this to 'YYYY-MM-DD HH:mm:ss' format .
>
> Thanks,
> Divya
>

Re: convert epoch time stamp to timestamp

Posted by Divya Gehlot <di...@gmail.com>.

When I
SELECT
CAST(FROM_UNIXTIME(t.`timestamp`) AS TIMESTAMP) AS `timestamp`
FROM
path/data/file  t limit 10 ;

49995-02-13T19:51:48.000Z
49995-04-22T15:47:05.000Z
49996-09-06T03:21:25.000Z
49997-03-05T01:20:19.000Z

I get these values .

Thanks,
Divya

On 15 January 2018 at 15:09, Divya Gehlot <di...@gmail.com> wrote:

> Hi ,
> One of field in my data file is  in epoch time stamp .
> The values would be similar like 1515545336591
>
> How can I convert this to 'YYYY-MM-DD HH:mm:ss' format .
>
> Thanks,
> Divya
>