Date and time data types
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 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 might discover that you can define an earlier timestamp value than 4713-01-01 00:00:00 BC, or a later one than 294276-01-01 00:00:00, without error. But you should not rely on this. Rather, you should accept that the values in the "Min" and "Max" columns in the table above specify the supported range.
Notice the "approx" qualifier by the minimum and maximum interval values.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.
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:
- Conceptual background
- Real timezones that observe Daylight Savings Time
- Real timezones that don't observe Daylight Savings Time
- The plain timestamp and timestamptz data types
- Sensitivity of converting between timestamptz and plain timestamp to the UTC offset
- Sensitivity of timestamptz-interval arithmetic to the current timezone
- Recommended practice for specifying the UTC offset
- Custom domain types for specializing the native interval functionality