Date and time data types and functionality

Synopsis

YSQL supports the following data types for values that represent a date, a time of day, a date-and-time-of-day pair, or a duration. These data types will be referred to jointly as the date-time data types.

Data type	Purpose	Internal format	Min	Max	Resolution
date	date moment (wall-clock)	4-bytes	4713 BC	5874897 AD	1 day
time [(p)]	time moment (wall-clock)	8-bytes	00:00:00	24:00:00	1 microsecond
timetz [(p)]	avoid this
timestamp [(p)]	date-and-time moment (wall-clock)	12-bytes	4713 BC	294276 AD	1 microsecond
timestamptz [(p)]	date-and-time moment (absolute)	12-bytes	4713 BC	294276 AD	1 microsecond
interval [fields] [(p)]	duration between two moments	16-bytes 3-field struct			1 microsecond

The optional (p) qualifier, where p is a literal integer value in 0..6, specifies the precision, in microseconds, with which values will be recorded. (It has no effect on the size of the internal representation.) The optional fields qualifier, valid only in an interval declaration, is explained in the interval data type section.

The spelling timestamptz is an alias, defined by PostgreSQL and inherited by YSQL, for what the SQL Standard spells as timestamp with time zone. The unadorned spelling, timestamp, is defined by the SQL Standard and may, optionally, be spelled as timestamp without time zone. A corresponding account applies to timetz and time.

Because of their brevity, the forms (plain) time, timetz, (plain) timestamp, and timestamptz are used throughout this "Date and time data types" main section rather than the verbose forms that spell the names using without time zone and with time zone.

A value of the interval data type represents a duration. In contrast, a value of one of the other five data types each represents a point in time (a.k.a. a moment).

Subtraction between a pair of moment values with the same data type produces, with one exception, an interval value. Exceptionally, subtracting one date value from another produces an integer value.

Avoid using the 'timetz' data type.

The PostgreSQL documentation recommends against using the timetz (a.k.a. time with time zone) data type. This text is slightly reworded:

The data type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, (plain) time, (plain) timestamp, and timestamptz should provide the complete range of date-time functionality that any application could require.

The thinking is that a notion that expresses only what a clock might read in a particular timezone gives only part of the picture. For example when a clock reads 20:00 in UTC, it reads 03:00 in China Standard Time. But 20:00 UTC is the evening of one day and 03:00 is in the small hours of the morning of the next day in China Standard Time. (Neither UTC nor China Standard Time adjusts its clocks for Daylight Savings.) The data type timestamptz represents both the time of day and the date and so it handles the present use case naturally. No further reference will be made to timetz.

Maximum and minimum supported values.

You can discover that you can define an earlier timestamp[tz] value than 4713-01-01 00:00:00 BC, or a later one than 294276-01-01 00:00:00, without error. Try this:

-- The domain "ts_t" is a convenient single point of maintenance to allow
-- choosing between "plain timestamp" and "timestamptz" for the test.
drop domain if exists ts_t cascade;
create domain ts_t as timestamptz;

drop function if exists ts_limits() cascade;
create function ts_limits()
  returns table(z text)
  language plpgsql
as $body$
declare
  one_sec  constant interval not null := make_interval(secs=>1);

  max_ts   constant ts_t     not null := '294276-12-31 23:59:59 UTC AD';
  min_ts   constant ts_t     not null :=   '4714-11-24 00:00:00 UTC BC';
  t                 ts_t     not null :=  max_ts;
begin
  z := 'max_ts: '||max_ts::text;                          return next;
  begin
    t := max_ts + one_sec;
  exception when datetime_field_overflow
    -- 22008: timestamp out of range
    then
      z := 'max_ts overflowed';                           return next;
  end;

  z := '';                                                return next;

  z := 'min_ts: '||min_ts::text;                          return next;
  begin
    t := min_ts - one_sec;
  exception when datetime_field_overflow
    -- 22008: timestamp out of range
    then
      z := 'min_ts underflowed';    return next;
  end;
end;
$body$;

set timezone = 'UTC';
select z from ts_limits();

This is the result:

 max_ts: 294276-12-31 23:59:59+00
 max_ts overflowed

 min_ts: 4714-11-24 00:00:00+00 BC
 min_ts underflowed

You see the same date and time-of-day values, but without the timezone offset of course, if you define the ts_t domain type using plain timestamp.

This test is shown for completeness. Its outcome is of little practical consequence. You can rely on the values in the "Min" and "Max" columns in the table above, copied from the PostgreSQL documentation, to specify the supported range. Yugabyte recommends that, as a practical compromise, you take these to be the limits for timestamp[tz] values:

['4713-01-01 00:00:00 BC', '294276-12-31 23:59:59 AD']

Notice that the minimum and maximum interval values are not specified in the table above. You need to understand how an interval value is represented internally as a three-field [mm, dd, ss] tuple to appreciate that the limits must be expressed individually in terms of these fields. The section interval value limits explains all this.

Modern applications almost always are designed for global deployment. This means that they must accommodate timezones—and that it will be the norm therefore to use the timestamptz data type and not date, plain time, or plain timestamp. Application code will therefore need to be aware of, and to set, the timezone. It's not uncommon to expose the ability to set the timezone to the user so that date-time moments can be shown differently according to the user's present purpose.

Special date-time manifest constants

PostgreSQL, and therefore YSQL, support the use of several special manifest text constants when they are typecast to specified date-time data types, thus:

constant	valid with
'epoch'	date, plain timestamp
'infinity'	date, plain timestamp, timestamptz
'-infinity'	date, plain timestamp
'now'	date, plain time, plain timestamp
'today'	date, plain timestamp
'tomorrow'	date, plain timestamp
'yesterday'	date, plain timestamp
'allballs'	plain time

Their meanings are given in section 8.5.1.4. Special Values in the PostgreSQL documentation.

Avoid using all of these special constants except for 'infinity' and '-infinity'.

The implementation of the function random_test_report_for_modeled_age() shows a common locution where 'infinity' and '-infinity' are used to initialize maximum and minimum values for timestamp values that are updated as new timestamp values arise during a loop's execution.

The constants 'infinity' and '-infinity' can be also used to define range values that are unbounded at one end. But this effect can be achieved with more clarity simply by omitting the value at the end of the range that you want to be unbounded.

The remaining special constants have different kinds of non-obvious results. See the recommendation Don't use the special manifest constant 'now' on the 'Functions that return the current date-time moment' page. The constants 'today', 'tomorrow'. and 'yesterday' all bring analogous risks to those brought by 'now'. And the intended effects of 'epoch' and 'allballs' are brought with optimal clarity for the reader by typecasting an appropriately spelled literal value to the required data type, whatever it might be.

Yugabyte recommends that you avoid using all of the special manifest text date-time constants except for 'infinity' and '-infinity'.

Even 'infinity' and '-infinity' can't be used everywhere that you might expect.

Try this test:

select 'infinity'::timestamptz - clock_timestamp();

It causes the 22008 error, cannot subtract infinite timestamps. Normally, the difference between two timestamptz values is an interval value. So you might think that the result here would be an infinite interval. But there is no such thing. This attempt:

select 'infinity'::interval;

causes the 22007 error, invalid input syntax for type interval: "infinity".

How to use the date-time data types major section

Many users of all kinds of SQL databases have reported that they find everything about the date-time story complex and confusing. This explains why this overall section is rather big and why the hierarchy of pages and child pages is both wide and deep. The order presented in the left-hand navigation menu was designed so that the pages can be read just like the sections and subsections in a book. The overall pedagogy was designed with this reading order in mind. It is highly recommended, therefore, that you (at least once) read the whole story from start to finish in this order.

If you have to maintain extant application code, you'll probably need to understand everything that this overall section explains. This is likely to be especially the case when the legacy code is old and has, therefore, been migrated from PostgreSQL to YugabyteDB.

However, if your purpose is only to write brand-new application code, and if you're happy simply to accept Yugabyte's various recommendations without studying the reasoning that supports these, then you'll need to read only a small part of this overall major section. This is what you need: