Set unix timestamp (int) as TIMESTAMP COLUMN in QuestDB

Question

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.

Alex des Pelagos · Accepted Answer · 2021-03-09 16:00:32Z

2

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

cast(unixDate * 1000000L AS TIMESTAMP)

answered Mar 9, 2021 at 16:00

Alex des Pelagos

1,5959 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jona Rodrigues Over a year ago

Thanks, could you point me to the right directions in the docs to get this 1000000L?Would love to see more of these formats for other similar issues.

Jona Rodrigues · Accepted Answer · 2021-03-06 03:26:02Z

1

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.

answered Mar 6, 2021 at 3:26

Jona Rodrigues

9801 gold badge12 silver badges27 bronze badges

Collectives™ on Stack Overflow

Set unix timestamp (int) as TIMESTAMP COLUMN in QuestDB

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related