Skip to content

Latest commit

 

History

History
296 lines (215 loc) · 9.83 KB

README-datetime.md

File metadata and controls

296 lines (215 loc) · 9.83 KB
Raw

Date and time functionalities in icu_ext

Postgres core provides a comprehensive set of types and functions that work with the widely used gregorian calendar, but does not support the traditional calendars used in some parts of the world. These calendars differ mostly by when they start, how many months there are in years and how they're named, and how many days there are in months.

Since the ICU library can handle many of these traditional calendars, icu_ext exposes them in Postgres through an alternate set of SQL functions, types and operators.

Locale settings

The calendar and the language used for date and time are defined through a locale string: language[_country][@calendar=caltype].

language and country are the usual short codes, as in en_US or fr_CA (see the output of icu_locales_list() for a full list). The choice of language selects the associated translations, and along with the country it influences how dates are displayed when using the basic formats with respect to cultural conventions (see the formatting options below).

Default values will be guessed from the environment when the language or calendar are not specified.

The accepted values for caltype are, as of ICU 70:

  • buddhist
  • chinese
  • coptic
  • dangi
  • ethiopic
  • ethiopic-amete-alem
  • gregorian
  • hebrew
  • indian
  • islamic
  • islamic-civil
  • islamic-rgsa
  • islamic-tbla
  • islamic-umalqura
  • iso8601
  • japanese
  • persian
  • roc

The locale can be passed to the icu_parse_date() and icu_format_date() functions, or assigned to the icu_ext.locale configuration setting to affect the behavior of the icu_date and icu_timestamptz types implemented by the extension.

Format strings for dates and timestamp

The fields available in the text representation of date and timestamps are described in Formatting Dates and Times (ICU documentation). The format strings composed of these fields are passed to icu_format_date, icu_parse_date, and used in the configuration settings icu_ext.timestamptz_format and icu_ext.date_format described below.

As an alternative to specifying individuals fields and separators, the format string can consist of a reference to a basic format, as described in the CLDR :

  • {short}
  • {medium}
  • {long}
  • {full}

The format code must be enclosed by curly brackets as shown in the list, with nothing else in the format string. When using these forms, which fields are displayed and in what order is determined by the language and country of the ICU locale.

These values match the ICU enum UDateFormatStyle

Dates can also be expressed relatively to the current day with the relative keyword added. The formats can be expressed as:

  • {short relative}
  • {medium relative}
  • {long relative}
  • {full relative}

Functions taking core types

icu_format_date (input date, format text [,locale text])

Return the string representing the input date with the given format and locale as described above. If locale is not specified, the current ICU locale is used.

Example:

=> select icu_format_date('2020-12-31'::date, '{medium}', 'en@calendar=ethiopic');
   icu_format_date
----------------------
 Tahsas 22, 2013 ERA1

icu_format_datetime (input timestamptz, format text [,locale text])

Return the string representing the time stamp wih time zone tswith the given format and locale as described above. If locale is not specified, the current ICU locale is used.

Example:

=> SELECT icu_format_datetime(
	 now(),
	 'GGGG dd/MMMM/yyyy HH:mm:ss.SSS z',
	 'fr@calendar=buddhist'
   );
              icu_format_datetime
------------------------------------------------
 ère bouddhique 22/septembre/2566 14:55:48.133 UTC+2

icu_parse_date (input text, format text [,locale text])

Return a date resulting from parsing the input string according to format (see "format strings" above).

The function will error out if the input string interpreted with the given format and locale does not strictly match the format or cannot be converted into a date. When locale is not specified, the current ICU locale is used.

Example:

=> SET icu_ext.locale TO '@calendar=buddhist';
=> SELECT icu_parse_date('25/09/2566', 'dd/MM/yyyy');
 icu_parse_date 
----------------
 2023-09-25

icu_parse_datetime (input text, format text [,locale text])

Return a timestamp with time zone resulting from parsing the input string according to format. This is similar to icu_parse_date() except that it parses a full timestamp instead of a date.

Example:

=> SELECT icu_parse_datetime(
  '11/Meskerem/2016 14:57:17',
  'dd/MMMM/yyyy HH:mm:ss',
  'en@calendar=ethiopic'
); 
   icu_parse_datetime   
------------------------
 2023-09-22 14:57:17+02

Custom types

icu_date

It differs from the core built-in type date in the input and output formats that are accepted. icu_date text representation works with respect to icu_ext.date_format if set, and otherwise with the default format of the current ICU locale. To express non-finite dates, use 'infinity'::date::icu_date.

Internally, the representation is the same as the date type, and icu_date can be cast implicitly to and from date.

Example:

 CREATE TABLE events(ev_name text, ev_date icu_date);

 INSERT INTO events VALUES('birthday', '2023-07-31'::date);

 SET icu_ext.locale TO 'orm@calendar=ethiopic';

 SELECT * FROM events;
 
 +----------+--------------------+
 | ev_name  |      ev_date       |
 +----------+--------------------+
 | birthday | 24-Hamle-2015 ERA1 |
 +----------+--------------------+

icu_timestamptz

It differs from the core built-in type timestamp with time zone (or timestamptz in short) in the input and output formats that are accepted. The text representation for icu_timestamptz works with respect to icu_ext.timestamp_format if set, and otherwise with the default format of the current ICU locale. To express non-finite timestamps, use 'infinity'::timestamptz::icu_timestamptz.

Internally, the representation is the same as the timestamptz type, and icu_timestamptz can be cast directly to and from timestamptz.

icu_interval

Like the interval built-in data type, it represents spans of time with years, months, days and microseconds components that are meant to process calendar-aware calculations.

It differs from interval in not assuming that one year always equals 12 months. For instance, in the ethiopic calendar, there are 13 months in a year. How spans of time are added to dates and timestamps depend on the current calendar. icu_interval accepts the same textual inputs as the interval data type. It also shares pretty much the same output except for not converting months to years.

icu_interval can be cast from interval.

Example:

select '25 months'::interval, '25 months'::icu_interval;
+---------------+--------------+
|   interval    | icu_interval |
+---------------+--------------+
| 2 years 1 mon | 25 mons      |
+---------------+--------------+

Operators

icu_interval * int

Multiply each component of the interval (years, months...) by the integer number. This operator is commutative.

icu_date + icu_interval

Add the years, months, days and time from the interval to the date, with respect to the rules of the calendar of the current locale (icu_ext.locale).

icu_date - icu_interval

Substract the years, months, days and time from the interval to the date, with respect to the rules of the calendar of the current locale (icu_ext.locale).

icu_timestamptz + icu_interval

Add the years, months, days and time from the interval to the timestamp, with respect to the rules of the calendar of the current locale (icu_ext.locale).

This operator is commutative.

icu_timestamptz - icu_interval

Subtract the years, months, days and time from the interval to the timestamp, with respect to the rules of the calendar of the current locale (icu_ext.locale).

icu_interval + icu_interval

Add the intervals. The result does not depend on the current calendar.

icu_interval - icu_interval

Subtract the intervals. The result does not depend on the current calendar.

Configurable settings

There are three configuration settings that work together to control input and output of the icu_date and icu_timestamptz types.

icu_ext.locale

Locale to use for input/output and calendar-dependent calculations, as described in "Locale format and settings" above.

-- vietnamese language, buddhist calendar
SET icu_ext.locale TO 'vi@calendar=buddhist';

SET icu_ext.timestamptz_format TO '{long}';

SELECT now()::icu_timestamptz;
                      now                       
------------------------------------------------
 Ngày 22 tháng 9 năm 2566 BE lúc 15:57:13 GMT+2

icu_ext.date_format

Format string used for the text representation of the icu_date datatype, both for input and output. The format is described in Formatting Dates and Times (ICU documentation).

The default value for this setting is {medium}.

icu_ext.timestamptz_format

Format string used for the text representation of the icu_timestamptz datatype, both for input and output. The format is described in Formatting Dates and Times (ICU documentation).

This setting also accepts the same references to basic formats (short, medium, ...) as icu_ext.date_format, and its default value is {medium}.