0 Replies Latest reply on Nov 30, 2017 3:11 AM by mnudel RSS
    mnudel Explorer

    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' ;