CODEWITHSUNDEEP
questdb
Jona Rodrigues
Jona Rodrigues

Reputation: 980

Set unix timestamp (int) as TIMESTAMP COLUMN in QuestDB

I have created a table coming from CSV upload which has a unix timestamp in the form of 1613775600. At import phase, QuestDB casts it as Int.

How can I set this field as the TIMESTAMP column ?

I tried this in my SELECT:

cast(unixDate AS TIMESTAMP)

but it does not correctly parses the unix date, giving inconsistent dates.

Upvotes: 1

Views: 781

Answers (2)

Alex des Pelagos
Alex des Pelagos

Reputation: 1465

This looks like seconds precision. QuestDb timestamp is in microseconds this cast should work

cast(unixDate * 1000000L AS TIMESTAMP)

Upvotes: 2

Jona Rodrigues
Jona Rodrigues

Reputation: 980

For now I used this trick:

dateadd('s', unixDate, to_timestamp('1970-01-01T00:00:00+0000', 'yyyy-MM-ddTHH:mm:ssZ'))

which basically adds XXXXXXXXXX seconds to the UTC date of January 1st, 1970.

Works quite well actually.

Upvotes: 1

Related Questions