cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Does PostgreSQL 'timestamp without timezone' field correspond to UTC time?

Highlighted
Contributor

Does PostgreSQL 'timestamp without timezone' field correspond to UTC time?

When daylight savings time (DST) had been applied, I was expecting to see a time difference of 1 hour between the actual event time on the machine (British Summer Time, BST) and that of the record stored in PostgreSQL database (value_stream). My experiments have shown that PostgreSQL stores data in UK time (as per system settings), not UTC, as anticipated. The problem in that case, however, is that whenever the time goes back, the data may be overwritten and lost. It could be that PostgreSQL stores values in UTC internally and presents data in accordance to regional settings, but how would anyone account for DST?! I'd be grateful if you could clarify on this matter. Thank you.

SELECT entry_id, entity_id, source_id, "time", property_type, property_name, property_value

  FROM value_stream

  WHERE entity_id = 'someHistorisation' AND source_id = 'someThing' AND property_name = 'someProperty' AND "time" >= '2017-10-29 00:00:00' AND "time" < '2017-10-29 04:00:00' ;

1 REPLY 1

Re: Does PostgreSQL 'timestamp without timezone' field correspond to UTC time?

Hi Michael, ValueStream will store the timestamp sent by the entity writing to that ValueStream, if this entity is something exiting on the ThingWorx Server it will use that timestamp, however if this entity is a remote edge device, I'd expect to see the timestamp from the edge device. Basically reviewing the Value_Stream table structure reveals that TimeZone information is being preserved as time column is of type "timestamp with time zone"

You are also right about the properties getting overwritten if the timestamps are exactly the same i.e. down to the miliseconds, as from the table structure time isn't a UID.

To preserve properties getting overwritten, you could consider converting the Timestamps to UTC prior to writing to the ValueStream or other option could be to use Stream which has a CONSTRAINT UNIQUE using (entity_id, source_id, source_type, "time")