Elizabeth Garrett Christensen: Working with Time in Postgres

Since humans first started recording data, they’ve been keeping track of time. Time management is one of those absolutely crucial database tasks and Postgres does a great job of it. Postgres has a lot of options for storing and querying time so I wanted to provide an overview of some of the most common needs for storing and retrieving time data. This blog is also available as a hands on tutorial running in a web browser. If you ask Postgres what time it is, SELECT now(); You’ll get something like now ----------------------------- 2023-05-15 18:23:58.5603+00 The default time representation here is a full timestamp string, containing the date, time, and a reference to timezone. In this case, the +00 represents equal with UTC. UTC has long been a standard time measurement following suit from the Greenwich mean time (if you’re as old as I am). If I want to know the time in my local timezone SELECT now() AT TIME ZONE 'America/Chicago'; The full list of timezones names you can use is stored in a system table and can be retrieved with select * from pg_timezone_names; Data types for time Postgres has a TIME data type, with and without a time zone if you want to store that separately from a date. This is generally not recommended since in most cases time requires an accompanying date. There’s a TIMESTAMP datatype. Adding timezone to TIMESTAMP is TIMESTAMP WITH TIMEZONE or aliased as the TIMESTAMPTZ. Without a doubt TIMESTAMPTZ is going to be the MVP of Postgres time storage. If you store data in with the full date, time, and timezone you’ll never have to worry about the server time, what time the user entered the data, what time it is where you’re querying data, or any of those crazy calculations. And you or your application can pull out the time and display it in whatever local user timezone you need. When working with Postgres, you’ll also see epoch which is how seconds are represented. This is not a timestamp, its an integer (a double precision floating-point number, 64 bits) [...]