Time zone storage in data type “timestamp with time zone”

by

This is just a misunderstanding stemming from the somewhat misleading type name. The time zone itself is not stored at all. It just acts as offset to compute a UTC timestamp (input), which is actually stored. Or as decorator in the display of a timestamp according to the current or given time zone (output). That’s all according to the SQL standard.

Just the point in time is stored, no zone information. That’s why 64 bit of information is enough. The timestamp is displayed to the client according to the current time zone setting of the session.

Details:

Also, since Jon mentioned it, time with time zone is defined in the SQL standard and thus implemented in Postgres, but its use is discouraged:

time with time zone is defined by the SQL standard, but the definition
exhibits properties which lead to questionable usefulness.

It’s an inherently ambiguous type that cannot deal with DST properly.

More Related Contents:

  1. Subtract hours from the now() function
  2. Auto increment table column
  3. Any downsides of using data type “text” for storing strings?
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. How to concatenate columns in a Postgres SELECT?
  6. How do I determine the last day of the previous month using PostgreSQL?
  7. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  8. Change PostgreSQL columns used in views
  9. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  10. How do you do date math that ignores the year?
  11. Casting NULL type when updating multiple rows
  12. How much disk-space is needed to store a NULL value using postgresql DB?
  13. PostgreSQL ORDER BY issue – natural sort
  14. How to store array or multiple values in one column
  15. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  16. Fetch records that are non zero after the decimal point in PostgreSQL
  17. Truncate timestamp to arbitrary intervals
  18. Possible to perform cross-database queries with PostgreSQL?
  19. Optimize query with OFFSET on large table
  20. GROUP BY and aggregate sequential numeric values
  21. How to use (install) dblink in PostgreSQL?
  22. PostgreSQL: encoding problems on Windows when using psql command line utility
  23. Aggregate a single column in query with many columns
  24. Aggregating (x,y) coordinate point clouds in PostgreSQL
  25. Does Postgres support nested or autonomous transactions?
  26. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  27. Update a column of a table with a column of another table in PostgreSQL
  28. Check if NULL exists in Postgres array
  29. How to detect query which holds the lock in Postgres?
  30. PostgreSQL: top n entries per item in same table

Leave a Comment