General

## 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

Accepted Solutions

## 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)```
1 ACCEPTED SOLUTION
8 REPLIES 8

## Re: TS to MS

Check this post from DNoeth.

## 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!

Multiply * 1000?

## Re: TS to MS

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

## Re: TS to MS

Doesn't work is a very precise error description.

Of course you can cast to a BigInt after multiplicdation.

## 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) ) )```

## 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)```

## 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.