---
title: "Table of contents for the date-time data types section"
url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/toc/"
---

# Table of contents for the date-time data types section

Overview and content map of the date-time types section.

## [The "Date and time data types and functionality" parent page](/stable/api/ysql/datatypes/type_datetime/ '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](/stable/api/ysql/datatypes/type_json/ "JSON data types and functionality") and [Array data types and functionality](/stable/api/ysql/datatypes/type_array/ "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](/stable/api/ysql/datatypes/type_datetime/conceptual-background/ "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](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "Converting between timestamptz and plain timestamp values").
- [Adding or subtracting an *interval* value to/from a *timestamptz* or plain *timestamp* value](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/moment-interval-overloads-of-plus-and-minus/ "Adding or subtracting an interval value to/from a timestamptz or plain timestamp value").

## [Timezones and UTC offsets](/stable/api/ysql/datatypes/type_datetime/timezones/ "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**](/stable/api/ysql/datatypes/type_datetime/timezones/catalog-views/ "The pg_timezone_names and pg_timezone_abbrevs catalog views")
- [**The extended\_timezone\_names view**](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/ "The extended_timezone_names view")
  
  - [**extended\_timezone\_names—unrestricted full projection**](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/unrestricted-full-projection/ "extended_timezone_names—unrestricted full projection")
  - [**Real timezones that observe Daylight Savings Time**](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/canonical-real-country-with-dst/ "Real timezones that observe Daylight Savings Time")
  - [**Real timezones that don't observe Daylight Savings Time**](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/canonical-real-country-no-dst/ "Real timezones that don't observe Daylight Savings Time")
  - [**Synthetic timezones (do not observe Daylight Savings Time)**](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/canonical-no-country-no-dst/ "Synthetic timezones (do not observe Daylight Savings Time)")
- [**Scenarios that are sensitive to the UTC offset or explicitly to the timezone**](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/ "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**](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "Sensitivity of converting between timestamptz and plain timestamp to the UTC offset")
  - [**Sensitivity of timestamptz-interval arithmetic to the current timezone**](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-interval-day-arithmetic/ "Sensitivity of timestamptz-interval arithmetic to the current timezone")
- [**Four ways to specify the UTC offset**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/ "Four ways to specify the UTC offset")
  
  - [**Rules for resolving a string that's intended to identify a UTC offset**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/ "Rules for resolving a string that's intended to identify a UTC offset")
    
    - [**Rule 1**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/rule-1/ "Rule 1") — It's resolved case-insensitively.
    - [**Rule 2**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/rule-2/ "Rule 2") — It's never resolved in *pg\_timezone\_names.abbrev*.
    - [**Rule 3**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/rule-3/ "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**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/rule-4/ "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**](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/helper-functions/ "Helper functions")
- [**Three syntax contexts that use the specification of a UTC offset**](/stable/api/ysql/datatypes/type_datetime/timezones/syntax-contexts-to-spec-offset/ "Three syntax contexts that use the specification of a UTC offset")
- [**Recommended practice for specifying the UTC offset**](/stable/api/ysql/datatypes/type_datetime/timezones/recommendation/ "Recommended practice for specifying the UTC offset")

## [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "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](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/ "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**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-date/ "The date data type")
- [**The time data type**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-time/ "The time data type")
- [**The plain timestamp and timestamptz data types**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-timestamp/ "The plain timestamp and timestamptz data types")
- [**The interval data type**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/ "The interval data type")
  
  - [**How does YSQL represent an interval value?**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-representation/ "How does YSQL represent an interval value?")
    
    - [**Ad hoc examples of defining interval values**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-representation/ad-hoc-examples/ "Ad hoc examples of defining interval values")
    - [**Modeling the internal representation and comparing the model with the actual implementation**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-representation/internal-representation-model/ "Modeling the internal representation and comparing the model with the actual implementation")
  - [**Interval value limits**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-limits/ "Interval value limits")
  - [**Declaring intervals**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/declaring-intervals/ "Declaring intervals")
  - [**The justify() and extract(epoch ...) functions for interval values**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/justfy-and-extract-epoch/ "The justify() and extract(epoch ...) functions for interval values")
  - [**Interval arithmetic**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/ "Interval arithmetic")
    
    - [**Comparing two interval values**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/interval-interval-comparison/ "Comparing two interval values")
    - [**Adding or subtracting a pair of interval values**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/interval-interval-addition/ "Adding or subtracting a pair of interval values")
    - [**Multiplying or dividing an interval value by a number**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/interval-number-multiplication/ "Multiplying or dividing an interval value by a number")
    - [**The moment-moment overloads of the "-" operator for timestamptz, timestamp, and time**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/moment-moment-overloads-of-minus/ '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**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/moment-interval-overloads-of-plus-and-minus/ 'The moment-interval overloads of the "+" and "-" operators for timestamptz, timestamp, and time')
  - [**Custom domain types for specializing the native interval functionality**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/custom-interval-domains/ "Custom domain types for specializing the native interval functionality")
  - [**User-defined interval utility functions**](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/ "User-defined interval utility functions")

## [Typecasting between values of different date-time data types](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-values/ "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](/stable/api/ysql/datatypes/type_datetime/operators/ "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**](/stable/api/ysql/datatypes/type_datetime/operators/test-date-time-comparison-overloads/ "Test the date-time comparison overloads")
- [**Test the date-time addition overloads**](/stable/api/ysql/datatypes/type_datetime/operators/test-date-time-addition-overloads/ "Test the date-time addition overloads")
- [**Test the date-time subtraction overloads**](/stable/api/ysql/datatypes/type_datetime/operators/test-date-time-subtraction-overloads/ "Test the date-time subtraction overloads")
- [**Test the date-time multiplication overloads**](/stable/api/ysql/datatypes/type_datetime/operators/test-date-time-multiplication-overloads/ "Test the date-time multiplication overloads")
- [**Test the date-time division overloads**](/stable/api/ysql/datatypes/type_datetime/operators/test-date-time-division-overloads/ "Test the date-time division overloads")

## [General-purpose date and time functions](/stable/api/ysql/datatypes/type_datetime/functions/ "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**](/stable/api/ysql/datatypes/type_datetime/functions/creating-date-time-values/ "Functions for creating date-time values")
- [**Functions for manipulating date-time values**](/stable/api/ysql/datatypes/type_datetime/functions/manipulating-date-time-values/ "Functions for manipulating date-time values")
- [**Functions that return the current date-time moment**](/stable/api/ysql/datatypes/type_datetime/functions/current-date-time-moment/ "Functions that return the current date-time moment")
- [**Functions for delaying execution**](/stable/api/ysql/datatypes/type_datetime/functions/delaying-execution/ "Functions for delaying execution")
- [**Miscellaneous date-time functions**](/stable/api/ysql/datatypes/type_datetime/functions/miscellaneous/ "Miscellaneous date-time functions")

## [Date and time formatting functions](/stable/api/ysql/datatypes/type_datetime/formatting-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](/stable/api/ysql/datatypes/type_datetime/formatting-functions/#date-time-template-patterns "Date-time template patterns"). These template patterns, again in turn, can be *modified*. See the section [Date-time template pattern modifiers](/stable/api/ysql/datatypes/type_datetime/formatting-functions/#date-time-template-pattern-modifiers "Date-time template pattern modifiers").

## [Case study—implementing a stopwatch with SQL](/stable/api/ysql/datatypes/type_datetime/stopwatch/ "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](/stable/api/ysql/datatypes/type_datetime/download-date-time-utilities/ "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.