drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arjun kr <arjun...@outlook.com>
Subject Re: convert epoch time stamp to timestamp
Date Mon, 15 Jan 2018 07:31:39 GMT
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 <divya.htconex@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 <divya.htconex@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
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message