[SOLVED] How to import time series data with the following timestamp format (2016-04-26 21:00:00+00)

How to import time series data with the following timestamp format (2016-04-26 21:00:00+00) - QuestDB

I have problems importing time series data with a specific timeseries format into QuestDB.

The timestamp is in the following format: 2016-04-26 21:00:00+00 and I try to import a csv file via the QuestDB web interface. I chose 'TIMESTAMP' for the respective column in the schema and a mask of 'yyyy-MM-dd HH:mm:ssX'. I also tried 'yyyy-MM-dd HH:mm:ssXX' and 'yyyy-MM-dd HH:mm:ss+XX' all unsuccessful.

QuestDB on the following page shows a unit of 'X' to represent ISO 8601 unit for hour offset from UTC.

QuestDB: Timestamp, date and time functions

What am I missing?

Solution

You can check timestamp format issue with to_timestamp() function because it returns null if string doesn't match format. In this case - yyyy-MM-dd HH:mm:ssZ or yyyy-MM-dd HH:mm:ssx (lowercase x only!) are the formats to use, e.g.

SELECT to_timestamp('2016-04-26 21:00:00+00', 'yyyy-MM-dd HH:mm:ssZ')

yields

2016-04-26T21:00:00.000000Z

The format above also accepts values such as:

2016-04-26 21:00:00+01
2016-04-26 21:00:00+00:00
2016-04-26 21:00:00+01:01