Convert timestamp into date and time Sql

Question

How can i convert a timestamp (formatted this way: 1,437,538,735.00) into date and time (such as 2016-11-23 14:00:00) in Teradata SQL?

Thank you!

Possible duplicate of [EXTRACT the date and time - (Teradata)](http://stackoverflow.com/questions/23506057/extract-the-date-and-time-teradata) — Mahesh, Nov 23 '16 at 03:03
@CoderofCode no it is not duplicate of that. here requirement is to convert epoch to teradata timestamp — Bhavesh Ghodasara, Nov 23 '16 at 08:25

score 0 · Answer 1 · answered Nov 23 '16 at 03:07

0

This looks like a Unix format for the time, so it is the number of seconds since 1970-01-01. Something like this should work:

select timestamp '1970-01-01 00:00:00' + interval '1437538735' second

I don't have Teradata on hand to specifically test the syntax.

answered Nov 23 '16 at 03:07

Gordon Linoff

I'm afraid I'm very new to Teradata so I'm no sure of the rest of the syntax. Would it be:CAST(timestamp '1970-01-01 00:00:00' + interval '1437538735') AS FINAL_DATE – user7172167 Nov 23 '16 at 03:14
problem is teradata not accepting more than 9999 with interval clause – Bhavesh Ghodasara Nov 23 '16 at 05:30

score 0 · Answer 2 · answered Nov 23 '16 at 05:45

0

Try this:

select CAST(DATE '1970-01-01' + (1437538735 / 86400) AS TIMESTAMP(0)) + ((1437538735 MOD 86400) * INTERVAL '00:00:01' HOUR TO SECOND) td_ts

answered Nov 23 '16 at 05:45

Bhavesh Ghodasara

David דודו Markovitz · Answer 3 · 2016-11-23T21:20:44.373

0

select timestamp '1970-01-01 00:00:00' + 1437538735 * interval '0 00:00:01' day to second

P.s.
With this syntax you can use a colum/expression instead of the hard-coded value

edited Nov 23 '16 at 21:20

answered Nov 23 '16 at 13:25

Should be `select timestamp '1970-01-01 00:00:00' + 1437538735 * interval '0000 00:00:01' day to second`, but it will fail anyway because it's more than 27 years :-) – dnoeth Nov 23 '16 at 13:51
Damn. Mobile. OOO :-) – David דודו Markovitz Nov 23 '16 at 13:53
@dnoeth, right of course regarding the 27 years but my other mistake was using`day(4)` in the first place. The bracket do no apply to literals. – David דודו Markovitz Nov 23 '16 at 21:22

3 Answers3