How do I convert Timestamp (having milliseconds) to EPOCH in Redshift

2
votes

How do I convert from timestamp (having milliseconds) to epoch

For E.g.,

Timestamp1 - 2019-10-20 11:43:47.298
Timestamp2 - 2019-10-20 11:43:47.469

Using EPOCH gives the same results for both the timestamps, even though they are different timestamps (different milliseconds)

Query-

extract('epoch' from timestamp '2019-10-20 11:43:47.298')::bigint * 1000
extract('epoch' from timestamp '2019-10-20 11:43:47.469')::bigint * 1000

Result - 1571571827000

I want different results as they have separate timestamps

1
sqldatetimestampamazon-redshiftepoch

1 Answers

2
votes

Just don't cast to bigint if you don't want to lose the millisecond precision:

select
    extract('epoch' from timestamp '2019-10-20 11:43:47.298') * 1000 epoch1,
    extract('epoch' from timestamp '2019-10-20 11:43:47.469') * 1000 epoch2

Demo on DB Fiddle:

epoch1        | epoch2       
:------------ | :------------
1571571827298 | 1571571827469