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,
edit: If possible, I would like to avoid time zones for simplicity… but Iif thats the way forward, then so be it.
is my interpretation correct that your goal is that all queried timestamps should be automatically converted to a specific timezone?
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.
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
For CrateDB you can use either
date_format() to convert to a local date time but you will need to type it out in the query if required.
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
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.