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.
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.
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
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
- Don’t set or use the environment variables
- 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
TimeZoneparameter 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.
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:
The values of these types are comparable, which is why you could order the last
end_date - start_date, which has a type of interval. Here’s another
Values of type timestamptz can also be subtracted (to give an interval), added (to an interval to give another timestamptz) and compared.
While on the topic, note that there are 3 different built-in functions that return various “current timestamp” values. They actually return different things:
There are also aliases for these functions:
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:
Other Functions And Constructs
PostgreSQL also comes with a few useful functions and constructs that can be used to manipulate values of these types.
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:
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:
Converting To And From Text
can convert dates, timestamps and intervals to text based on a format string –
the Postgres equivalent of the classic C function
For converting from text to dates use
to_date, and for converting text to
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.
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.).
make_interval function can be used to create an interval value from
an integral number of component values (see docs here).
to_timestamp function we saw earlier has another form that can create a
timestamptz value from Unix epoch time.
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 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.