Table of contents for the date-time data types section

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

The "Date and time data types and functionality" parent page

This section is the top of the entire date-time documentation subtree. Its siblings are the top-of-subtree pages for other data types like JSON data types and functionality and Array data types and functionality.

It presents a table that summarizes the properties of the date-time data types, and that links to the dedicated sections for each of these. It recommends avoiding the timetz data type and states that it will not, therefore, be treated in the data-time major section.

For completeness, it presents a table of the special date-time manifest constants and recommends that you avoid using all of these except for 'infinity' and '-infinity'.

Finally, it lists the date-time subsections that cover just those topics that you will need to understand if your purpose is only to write brand-new application code.

Conceptual background

This section explains the background for the accounts of the date-time data types. In particular, it explains the notions that underly the sensitivity to the reigning timezone of these operations:

Timezones and UTC offsets

This section explains: the purpose and significance of the set timezone SQL statement; the at time zone operator for plain timestamp and timestamptz expressions; the various other ways that, ultimately, the intended UTC offset is specified; and which operations are sensitive to the specified UTC offset. It has these child pages:

Typecasting between date-time values and text values

Many of the code examples rely on typecasting—especially from/to text values to/from plain timestamp and timestamptz values. It's unlikely that you'll use such typecasting in actual application code. (Rather, you'll use dedicated built-in functions for the conversions.) But you'll rely heavily on typecasting for ad hoc tests while you develop such code.

The semantics of the date-time data types

This section defines the semantics of the date data type, the time data type, the plain timestamp and timestamptz data types, and the interval data type. Interval arithmetic is rather tricky. This explains the size of the subsection that's devoted to this data type. The section has these child pages:

Typecasting between values of different date-time data types

This section presents the five-by-five matrix of all possible conversions between values of the date-time data types. Many of the cells are empty because they correspond to operations that aren't supported (or, because the cell is on the diagonal representing the conversion between values of the same data type, it's tautologically uninteresting). This still leaves twenty typecasts whose semantics you need to understand. However, many can be understood as combinations of others, and this leaves only a few that demand careful study. The critical conversions are between plain timestamp and timestamptz values in each direction.

Date and time operators

This section describes the date-time operators and presents tests for them grouped as follows:

General-purpose date and time functions

This section describes the general-purpose date-time functions in the following groups:

Date and time formatting functions

This section describes:

  • The use of the to_char() built-in function for converting a date-time value to a text value.
  • The use of the to_date() and to_timestamp() built-in functions for converting a text value to a date-time value.

The conversions, in each direction, are controlled by a so-called template. A template, in turn, is made up of a mixture of pre-defined so-called template patterns and free text in a user-defined order. See the section Date-time template patterns. These template patterns, again in turn, can be modified. See the section Date-time template pattern modifiers.

Case study—implementing a stopwatch with SQL

This shows you how to implement a SQL stopwatch that allows you to start it with a procedure call before starting what you want to time and to read it with a select statement when what you want to time finishes. This reading goes to the spool file along with all other select results. Using a SQL stopwatch brings many advantages over using \timing on.

Download and install the date-time utilities code

This short page gives the instructions for downloading and installing all of the reusable code that's defined within this date-time data types major section.