PostgreSQL comes with a bunch of built-in date and time related data types. Why should you use them over strings or integers? What should you watch out for while using them? Read to learn more about how to work effectively with these data types in Postgres.

A Whole Lot of Types

The SQL standard, the ISO 8601 standard, PostgreSQL’s built-in catalog and backward compatibility together define a plethora of overlapping, customizable date/time related data types and conventions that is confusing at best. This confusion typically spills over into database driver code, application code, SQL routines and results in subtle bugs that are difficult to debug.

On the other hand, using native built-in types simplify SQL statements and make them much easier to read and write, and consequently, less error-prone. Using, say integers (number of seconds since epoch) to represent time, results in unwieldy SQL expressions and more application code.

The benefits of native types make it worthwhile to define a set of not-so-painful rules and enforce them throughout the application and ops code base. Here is one such set, which should provide sane defaults and a resonable starting point for further customization if required.

Types

Use only the following 3 types (although many are available):

  • date - a specific date, without time
  • timestamptz - a specific date and time with microsecond resolution
  • interval - a time interval with microsecond resolution

These three types together should support most application use cases. If you do not have specific needs (like conserving storage), it is highly recommended to stick to just these types.

The date represents a date without time, and is quite useful in practice (see examples below). The timestamp type is the variant that includes the timezone information – without the timezone information there are simply too many variables that can affect the interpretation and extraction of the value. Finally, the interval represents time intervals from as low as a microsecond upto millions of years.

Literal Strings

Use only the following literal representations, and use the cast operator to reduce verbosity without sacrificing readability:

  • '2012-12-25'::date - ISO 8601
  • '2012-12-25 13:04:05.123-08:00'::timestamptz - ISO 8601
  • '1 month 3 days'::interval - Postgres traditional format for interval input

Omitting the time zone leaves you at the mercy of the Postgres server’s timezone setting, the TimeZone configuration that can be set at database-level, session-level, role-level or in the connection string, the client machine’s timezone setting, and more such factors.

While querying from application code, convert interval types to a suitable unit (like days or seconds) using the extract function and read in the value as an integer or real value.

Configuration And Other Settings

  • Don’t change the default settings for the GUC configuration DateStyle, TimeZone and lc_time.
  • Don’t set or use the environment variables PGDATESTYLE and PGTZ.
  • Don’t use SET [SESSION|LOCAL] TIME ZONE ....
  • If you can, set the system timezone to UTC on the machine that runs the Postgres server, as well as all the machines running application code that connect to it.
  • Validate that your database driver (like a JDBC connector, or a Go database/sql driver) behaves sensibly while the client is running on one timezone and the server on another. Ensure it works correctly when a valid non-UTC TimeZone parameter is included in the connection string.

Finally, note that all these are only guidelines and can be tweaked to suit your needs – but make sure you investigate the implications of doing so first.

Native Types and Operators

So how exactly does using native types help in simplifying SQL code? Here are a few examples.

Date Type

Values of the date type can be subtracted to give the interval between them. You can also add an integer number of days to a particulate date, or add an interval to a date to give a timestamptz:

-- 10 days from now (outputs 2020-07-26)
SELECT now()::date + 10;
 
-- 10 days from now (outputs 2020-07-26 04:44:30.568847+00)
SELECT now() + '10 days'::interval;

-- days till christmas (outputs 161 days 14:06:26.759466)
SELECT '2020-12-25'::date - now();

-- the 10 longest courses
  SELECT name, end_date - start_date AS duration
    FROM courses
ORDER BY end_date - start_date DESC
   LIMIT 10;

The values of these types are comparable, which is why you could order the last query by end_date - start_date, which has a type of interval. Here’s another example:

-- certificates expiring within the next 7 days
SELECT name
  FROM certificates
 WHERE expiry_date BETWEEN now() AND now() + '7 days'::interval;

Timestamp Type

Values of type timestamptz can also be subtracted (to give an interval), added (to an interval to give another timestamptz) and compared.

-- difference of timestamps gives an interval
SELECT password_last_modified - created_at AS password_age
  FROM users;

-- can also use the age() function
SELECT age(password_last_modified, created_at) AS password_age
  FROM users;

While on the topic, note that there are 3 different built-in functions that return various “current timestamp” values. They actually return different things:

-- transaction_timestamp() returns the timestampsz of the start of current transaction
-- outputs 2020-07-16 05:09:32.677409+00
SELECT transaction_timestamp();

-- statement_timestamp() returns the timestamptz of the start of the current statement
SELECT statement_timestamp();

-- clock_timestamp() returns the timestamptz of the system clock
SELECT clock_timestamp();

There are also aliases for these functions:

-- now() actually returns the start of the current transaction, which means it
-- does not change during the transaction
SELECT now(), transaction_timestamp();

-- transaction timestamp is also returned by these keyword-style constructs
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, transaction_timestamp();

Interval Types

Interval-typed values can be used as column data types, can be compared with each other and can be added to (and subtracted from) timestamps and dates. Here are a few examples:

-- interval-typed values can be stored and compared 
  SELECT num
    FROM passports
   WHERE valid_for > '10 years'::interval
ORDER BY valid_for DESC;

-- you can multiply them by numbers (outputs 4 years)
SELECT 4 * '1 year'::interval;

-- you can divide them by numbers (outputs 3 mons)
SELECT '1 year'::interval / 4;

-- you can add and subtract them (outputs 1 year 1 mon 6 days)
SELECT '1 year'::interval + '1.2 months'::interval;

Other Functions And Constructs

PostgreSQL also comes with a few useful functions and constructs that can be used to manipulate values of these types.

Extract

The extract function can be used to retrieve a specified part from the given value, like the month from a date. The full list of parts that can be extracted is documented here. Here are a few useful and non-obvious examples:

-- years from an interval (outputs 2)
SELECT extract(YEARS FROM '1.5 years 6 months'::interval);

-- day of the week (0=Sun .. 6=Sat) from timestamp (outputs 4)
SELECT extract(DOW FROM now());

-- day of the week (1=Mon .. 7=Sun) from timestamp (outputs 4)
SELECT extract(ISODOW FROM now());

-- convert interval to seconds (outputs 86400)
SELECT extract(EPOCH FROM '1 day'::interval);

The last example is particularly useful in queries run by applications, as it can be easier for applications to handle an interval as a floating-point value of the number of seconds/minutes/days/etc.

Time Zone Conversion

There is also a handy function to express a timestamptz in another time zone. Typically this would be done in the application code – it is easier to test that way, and reduces the dependency on the time zone database that the Postgres server will refer to. Nevertheless, it can be useful at times:

-- convert timestamps to a different time zone
SELECT timezone('Europe/Helsinki', now());

-- same as before, but this one is a SQL standard
SELECT now() AT TIME ZONE 'Europe/Helsinki';

Converting To And From Text

The function to_char (docs) can convert dates, timestamps and intervals to text based on a format string – the Postgres equivalent of the classic C function strftime.

-- outputs Thu, 16th July
SELECT to_char(now(), 'Dy, DDth Month');

-- outputs 01 06 00 12 00 00
SELECT to_char('1.5 years'::interval, 'YY MM DD HH MI SS');

For converting from text to dates use to_date, and for converting text to timestamps use to_timestamp. Note that if you use the forms that were listed in the beginning of this post, you can just use the cast operators instead.

-- outputs 2000-12-25 15:42:50+00
SELECT to_timestamp('2000.12.25.15.42.50', 'YYYY.MM.DD.HH24.MI.SS');

-- outputs 2000-12-25
SELECT to_date('2000.12.25.15.42.50', 'YYYY.MM.DD');

See the docs for the full list of format string patterns.

It is best to use the these functions for simple cases. For more complicated parsing or formatting, it is better to rely on application code, which can (arguably) be better unit-tested.

Interfacing With Application Code

It is sometimes not convenient to pass date/timestamptz/interval values to and from application code, especially when bound parameters are used. For example, it is usually more convenient to pass an interval as an integer number of days (or hours, or minutes) rather than in a string format. It is also easier to read in an interval as a integer/floating-point number of days (or hours, or minutes etc.).

The make_interval function can be used to create an interval value from an integral number of component values (see docs here). The to_timestamp function we saw earlier has another form that can create a timestamptz value from Unix epoch time.

-- pass the interval as number of days from the application code
SELECT name FROM courses WHERE duration <= make_interval(days => $1);

-- pass timestamptz as unix epoch (number of seconds from 1-Jan-1970)
SELECT id FROM events WHERE logged_at >= to_timestamp($1);

-- return interval as number of days (with a fractional part)
SELECT extract(EPOCH FROM duration) / 60 / 60 / 24;

About pgDash

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.

pgDash Queries Dashboard

pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here or signup today for a free trial.