TS to MS

General
Enthusiast

TS to MS

Can you please help in converting timestamp column to milliseconds column?

 

Input:

08/05/2018 17:18:10.455000

 

Output should be as below:

1,525,799,890,455

 

 

8 REPLIES
Teradata Employee

Re: TS to MS

Check this post from DNoeth.

Enthusiast

Re: TS to MS

Thanks Fred. 

 

But the output is displayed with decimals. I would need it without decimals.

 

Something like this, 1,525,799,890,455

and 1,525,799,890.455000

SyntaxEditor Code Snippet

(CAST((CAST(ts AS DATE) - DATE '1970-01-01') AS decimal(18,6)) * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)+ (EXTRACT(MINUTE FROM ts) * 60)+ (EXTRACT(SECOND FROM ts)  ))

 

 

Can you help please!

 

Junior Contributor

Re: TS to MS

Multiply * 1000?

Enthusiast

Re: TS to MS

Nope Dnoeth, it doesnt work when I multiply by 1000.

Junior Contributor

Re: TS to MS

Doesn't work is a very precise error description.

 

Of course you can cast to a BigInt after multiplicdation.

Enthusiast

Re: TS to MS

Sorry fro not being precise.

 

Am expecting output to be something like,

1525799890455

 

whereas my query below gives me as 1525799890.455

 

Casting it to bigint leave last 3 values, and thats not expected too. Multiplying doesnt work too. Might be silly, but am stuck somewhere here.

 

SyntaxEditor Code Snippet

(CAST((CAST(ts AS DATE) - DATE '1970-01-01') AS decimal(18,6)) * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)+ (EXTRACT(MINUTE FROM ts) * 60)+ (EXTRACT(SECOND FROM ts) ) )

 

Junior Contributor

Re: TS to MS

Multiply first, then cast:

Cast((Cast((Cast(ts AS DATE) - DATE '1970-01-01') AS DECIMAL(38,6)) * 86400
    + (Extract(HOUR From ts) * 3600)+ (Extract(MINUTE From ts) * 60)+ (Extract(SECOND From ts)  )) * 1000 AS BIGINT)
Enthusiast

Re: TS to MS

Thanks, it worked now!

 

One quick clarification,Can we use strtok function for null values? 

 

If no, other than CsvLd, do we have any other functions? Columns are predefined and no.of columns will not differ.