Table of contents for the date-time data types section

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 datatype 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:

• Converting between timestamptz and plain timestamp values.
• Adding or subtracting an interval value to/from a timestamptz or plain timestamp value.

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:

• The pg_timezone_names and pg_timezone_abbrevs catalog views
• The extended_timezone_names view
  • extended_timezone_names—unrestricted full projection
  • Real timezones that observe Daylight Savings Time
  • Real timezones that don't observe Daylight Savings Time
  • Synthetic timezones (do not observe Daylight Savings Time)
• Scenarios that are sensitive to the UTC offset or explicitly to the timezone
  • Sensitivity of converting between timestamptz and plain timestamp to the UTC offset
  • Sensitivity of timestamptz-interval arithmetic to the current timezone
• Four ways to specify the UTC offset
  • Rules for resolving a string that's intended to identify a UTC offset
    • Rule 1 — It's resolved case-insensitively.
    • Rule 2 — It's never resolved in pg_timezone_names.abbrev.
    • Rule 3 — It's never resolved in pg_timezone_abbrevs.abbrev as the argument of set timezone but is resolved there as the argument of at time zone (and, equivalently, in timezone()) and as the argument of make_timestamptz() (and equivalently within a text literal for a timestamptz value).
    • Rule 4 — It's is resolved first in pg_timezone_abbrevs.abbrev and, only if this fails, then in pg_timezone_names.name. This applies only in those syntax contexts where pg_timezone_abbrevs.abbrev is a candidate for the resolution—so not for set timezone, which looks only in pg_timezone_names.name.
    • Helper functions
• Three syntax contexts that use the specification of a UTC offset
• Recommended practice for specifying the UTC offset

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:

• The date data type
• The time data type
• The plain timestamp and timestamptz data types
• The interval data type
  • How does YSQL represent an interval value?
    • Ad hoc examples of defining interval values
    • Modeling the internal representation and comparing the model with the actual implementation
  • Interval value limits
  • Declaring intervals
  • The justify() and extract(epoch ...) functions for interval values
  • Interval arithmetic
    • Comparing two interval values
    • Adding or subtracting a pair of interval values
    • Multiplying or dividing an interval value by a number
    • The moment-moment overloads of the "-" operator for timestamptz, timestamp, and time
    • The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time
  • Custom domain types for specializing the native interval functionality
  • User-defined interval utility functions

Typecasting between values of different date-time datatypes

This section presents the five-by-five matrix of all possible conversions between values of the date-time datatypes. 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:

• Test the date-time comparison overloads
• Test the date-time addition overloads
• Test the date-time subtraction overloads
• Test the date-time multiplication overloads
• Test the date-time division overloads

General-purpose date and time functions

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

• Functions for creating date-time values
• Functions for manipulating date-time values
• Functions that return the current date-time moment
• Functions for delaying execution
• Miscellaneous date-time functions

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.