PolarDB:Date/Time types

Supported types

Type aliases:

• timestamp is equivalent to timestamp without time zone per the SQL standard.

• timestamptz is an accepted abbreviation for timestamp with time zone (a PostgreSQL extension).

YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
YEAR TO MONTH | DAY TO HOUR | DAY TO MINUTE | DAY TO SECOND
HOUR TO MINUTE | HOUR TO SECOND | MINUTE TO SECOND

Choose a type

Avoid time with time zone. The SQL standard defines it, but time zone offsets have little meaning without an associated date—daylight saving time boundaries vary by date. Use date, time, timestamp without time zone, and timestamp with time zone to cover all date/time needs.

timestamp variants: timestamp without time zone stores the value as given, with no time zone conversion. timestamp with time zone stores values internally in UTC and converts them to the session time zone on display.

Date input

Date and time literals must be enclosed in single quotes. The SQL syntax is:

type [ (p) ] 'value'

Date input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, and traditional PostgreSQL formats. For ambiguous formats, set the DateStyle parameter to control the interpretation order:

• MDY — month/day/year

• DMY — day/month/year

• YMD — year/month/day

Time input

time alone is equivalent to time without time zone. If a time zone is included in the input for time without time zone, it is silently ignored.

When using a time zone name that involves daylight saving time rules (such as America/New_York), specify the date as well so the engine can determine whether standard or daylight saving time applies. The resulting offset is stored as-is in a time with time zone value and is not adjusted to the active session time zone.

Time zone input formats:

Timestamp input

Valid timestamp input concatenates a date and a time, followed by an optional time zone and optional AD or BC. The following are all valid:

1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00
January 8 04:05:06 1999 PST

How PostgreSQL determines the type from a literal:

PostgreSQL does not inspect the literal string content to determine its type. Both of the following are treated as timestamp without time zone:

TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP '2004-10-19 10:23:54+02'   -- time zone offset is ignored

To store a value as timestamp with time zone, specify the type explicitly:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

How timestamp with time zone stores values:

All timestamp with time zone values are stored internally in UTC. On input, explicit time zone offsets are converted to UTC. If no time zone is specified, the system's TimeZone parameter is assumed and the value is converted to UTC. On output, the stored UTC value is converted to the current timezone and displayed as local time.

To view a timestamp in a different time zone, either change the timezone session variable or use the AT TIME ZONE operator:

SELECT '2004-10-19 10:23:54+02'::timestamptz AT TIME ZONE 'America/New_York';

Conversions between timestamp without time zone and timestamp with time zone treat the timestamp without time zone value as being in the current timezone local time. Specify a different time zone with AT TIME ZONE.

Special values

The following special input strings are recognized for date and time types. infinity and -infinity are stored as distinct internal values; the rest are shorthand notations converted to ordinary date/time values when read.

The SQL-standard functions CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP return the current date or time for the corresponding type. These are SQL functions and are not recognized in data input strings.

Date/time output

Set the output format with the DateStyle parameter. Four styles are available; the default is ISO.

ISO 8601 specifies uppercase T between date and time. PostgreSQL accepts that on input but outputs a space instead, for readability and consistency with RFC 3339.

The datestyle parameter also controls input ordering for ambiguous formats:

For more flexible formatting, use the to_char function.

Time zones

PolarDB for Oracle uses the IANA (Olson) time zone database for historical time zone rules. All timezone-aware values are stored internally in UTC and converted to the time zone specified by the TimeZone parameter before display.

Three forms are accepted when specifying a time zone:

• Full time zone name — for example, America/New_York. Recognized names are listed in the pg_timezone_names view. Full names encode daylight saving time transition rules, so the UTC offset they represent can vary by date.

• Time zone abbreviation — for example, PST. Abbreviations represent a fixed UTC offset and do not encode daylight saving time rules. Recognized abbreviations are listed in the pg_timezone_abbrevs view. You cannot use abbreviations as the value of the TimeZone or log_timezone configuration parameters, but they are accepted in date/time input values and with the AT TIME ZONE operator.

• POSIX-style specification — use this only when no suitable IANA time zone entry is available.

Abbreviations vs. full names: The difference matters when daylight saving time is in effect. For example, 2014-06-04 12:00 America/New_York and 2014-06-04 12:00 EDT both represent noon Eastern Daylight Time (UTC-4) on that date. But 2014-06-04 12:00 EST always means noon Eastern Standard Time (UTC-5), regardless of the date—so on June 4 it refers to a different instant in time.

Some jurisdictions have used the same abbreviation for different UTC offsets at different times. For example, MSK has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to what they meant on the specified date.

Time zone names and abbreviations are recognized case-insensitively and are loaded from configuration files under .../share/timezone/ and .../share/timezonesets/.

Setting the session time zone:

-- Set time zone for the current session
SET TIME ZONE 'America/New_York';

-- Equivalent syntax
SET TIMEZONE TO 'America/New_York';

The PGTZ environment variable can be set by libpq clients to send a SET TIME ZONE command automatically upon connection.

Interval input

Interval values can be written in three formats.

Verbose syntax:

[@] quantity unit [quantity unit...] [direction]

• quantity — a number, optionally signed

• unit — microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or their abbreviations and plurals

• direction — ago (negates all fields) or empty

• The leading @ is optional

Days, hours, minutes, and seconds can be specified without unit labels. '1 12:59:10' is equivalent to '1 day 12 hours 59 min 10 sec'. A combination of years and months can be written with a dash: '200-10' is equivalent to '200 years 10 months'.

ISO 8601 format with designators:

P quantity unit [quantity unit...] [T [quantity unit...]]

The string must begin with P. The T separator introduces time-of-day units. The meaning of M depends on whether it appears before or after T.

ISO 8601 alternative format:

P [years-months-days] [T hours:minutes:seconds]

Input examples:

Field specifier interaction:

When writing an interval with a field specifier, unmarked quantities are interpreted relative to the least significant field. INTERVAL '1' YEAR reads as 1 year; INTERVAL '1' reads as 1 second. Field values beyond the least significant allowed field are silently discarded: INTERVAL '1 day 2:03:04' HOUR TO MINUTE drops the seconds field but retains the day field.

Mixed-sign intervals:

According to the SQL standard, all fields of an interval must share the same sign. PostgreSQL traditionally allows fields to have different signs and treats each field as independently signed. If IntervalStyle is set to sql_standard, a leading sign is considered to apply to all fields (but only if no additional signs appear); otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity when any field is negative, attach an explicit sign to each field.

Fractional units:

Interval values support fractional input, but the internal representation uses only three integer units: months, days, and microseconds.

• Fractional units larger than months are truncated to whole months: '1.5 years' becomes '1 year 6 mons'.

• Fractional weeks and days are converted assuming 30 days per month and 24 hours per day: '1.75 months' becomes 1 mon 22 days 12:00:00.

• Only seconds are displayed as fractional on output.

This storage model can produce unexpected results when extracting components:

SELECT EXTRACT(hours FROM '80 minutes'::interval);
-- Result: 1

SELECT EXTRACT(days FROM '80 hours'::interval);
-- Result: 0

Use justify_days and justify_hours to normalize days and hours that overflow their standard ranges.

Interval output

Set the interval output format with SET intervalstyle. The default is postgres.

The sql_standard style produces SQL-standard-compliant output when the interval value is year-month only or day-time only with no mixed signs. Otherwise it outputs a year-month literal string followed by a day-time literal string with explicit signs to disambiguate.