Timestamp without time zone in local time

CrateDB SQL
1

Howdy folks,

I am logging machine data and would like to have a time stamp with local time from the machine. The (generated) column is created as

ts_crate TIMESTAMP WITHOUT TIME ZONE GENERATED ALWAYS AS CURRENT_TIMESTAMP

and when I query the last entries with

SELECT * from mibrecords WHERE ts_crate >= CURRENT_TIMESTAMP - INTERVAL '1' MINUTE;

I get the proper results although the timestamp is in UTC not GMT+1.

What is the best practice to get the right timestamps? Re-converting with every query seems clunky and offsets are sure to be difficult with summer / winter time.

Thanks a lot for your insights,
Frank

edit: If possible, I would like to avoid time zones for simplicity… but Iif thats the way forward, then so be it.

2

Hi @inviridi,

is my interpretation correct that your goal is that all queried timestamps should be automatically converted to a specific timezone?

3

Hi Jayeff,

yes, you are correct.

I am able to convert the timestamps after querying and I know how to add an offset, but I am susceptible to overengineering and would just like know how you would approach this. If I could just query

SELECT * FROM table

and the proper timestamps would be shown, that’d be awesome. Then again, I am not a SQL guy and if UTC is what everybody is using, then I would go with this.

4

Hi @inviridi,

in general it’s useful to stick to UTC. That’s why CrateDB (but also other databases e.g. PostgreSQL) will always store timestamps in UTC. Converting to a specific time zone is typically more a client concern (which can be you connecting to the database via command line interface).

PostgreSQL allows to to set a timezone for the current session (SET timezone TO 'Europe/Vienna';) and all timestamptz will be returned in the defined timezone. This features isn’t currently supported by CrateDB though :pensive:

For CrateDB you can use either timezone() or date_format() to convert to a local date time but you will need to type it out in the query if required.

SELECT
  timezone('Europe/Vienna', now())::timestamptz AS datetime1,
  date_format('%Y-%m-%dT%H:%i:%s.%fZ', 'Europe/Vienna', now()) AS datetime2;

I hope this still helps you a bit

2 Likes
5

Hi @jayeff,

Many thanks for your swift reply and the code! I will stick to the UTC timestamps if that’s the generally accepted approach. Also, I am happy to report success with your code … it is fine with me to include it my queries. I did some benchmarking already and converting from UTC to another timezone overall adds 5-15ms which is totally acceptable for my application.

Cheers,
Frank

2 Likes