Ephoc to teradata timestamp

Database
Enthusiast

Ephoc to teradata timestamp

Hi ,

i have an a file with date column

EMP_join_date

1454247163111

1453315790000

i have to convert into teradata timestamp.

i have used below query

,CAST(DATE '1970-01-01' + (cast(EMP_join_date as bigint)/86400000) AS TIMESTAMP(0))

+ (((cast(EMP_join_date as bigint)/ 1000) MOD 86400) * INTERVAL '00:00:01.000' HOUR TO SECOND)

but above query is giving  different  results, if we run daily.

Could you please provide query to get correct date .

7 REPLIES
Junior Supporter

Re: Ephoc to teradata timestamp

Hi.

There's something wrong with your data: 1454247163111 seconds are more than 4600 years (not a 'real' epoch as seconds since 1970-01-01 00:00:00).

If you have milliseconds, then you can do:

SELECT TD_SYSFNLIB.TO_TIMESTAMP(CAST(1454247163111/1000 AS INTEGER));

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

TO_TIMESTAMP((1454247163111./1000))

-----------------------------------

2016-01-31 13:32:43.000000

HTH.

Cheers.

Carlos.

Enthusiast

Re: Ephoc to teradata timestamp

Thanks Carlos.

can i know why we use 1970-01-01 date here.

,CAST(DATE '1970-01-01' + (cast(EMP_join_date as bigint)/86400000) AS TIMESTAMP(0))

+ (((cast(EMP_join_date as bigint)/ 1000) MOD 86400) * INTERVAL '00:00:01.000' HOUR TO SECOND)

and in my file i have

EMP_join_date

1454247163111

-99

1453315790000

i have to write case statement if EMP_join_date is -99 then it should display -99,otherwise convert it to timestamp value.

SELECT 
,case when EMP_join_date=-99 then '-99'  else TO_TIMESTAMP(CAST(EMP_join_date AS BIGINT)/1000) + CAST(EMP_join_date AS BIGINT) MOD 1000 * INTERVAL '0.001' SECOND end EMP_join_date
from tablename;

i have used above query, but iam getting error 3800:Datatype mismatch in THEN/ELSE expression.

could you please help me in fixing above error.

Junior Supporter

Re: Ephoc to teradata timestamp

Hi.
CASE cannot give different types. The result type of every condition must be the same.
You may use VARCHAR for the two of them.

HTH.

Cheers.

Carlos.
Senior Apprentice

Re: Ephoc to teradata timestamp

You might simply use a NULL timestamp instead of '-99'.

DATE '1970-01-01' is used because you wrote that it's a Epoch:

https://en.wikipedia.org/wiki/Unix_epoch

Enthusiast

Re: Ephoc to teradata timestamp

can please provide sample sql

Senior Apprentice

Re: Ephoc to teradata timestamp

case when EMP_join_date=-99 then NULL else ... 
Enthusiast

Re: Ephoc to teradata timestamp

Thanks Dieter :)