Time Literals

Use time literals to specify timestamps and intervals.

Timestamp Literals

The syntax of a timestamp literal is:

TIMESTAMP 'YYYY-MM-DD [HH:MI[:SS[.FF]]]'

Where:

YYYY-MM-DD are numeric designations of the year, month, and day.
HH:MI are numeric designations for hour and minute.
:SS is a designation for seconds, used only if the hour and minute are specified.
.FF is a designation for fractions of a second, using zero to six digits and only if seconds are specified.

Use one or more blank spaces to separate the date from the time specification.

Some valid timestamps are:

TIMESTAMP '2002-03-12 06:05:32.474003'
TIMESTAMP '2005-02-01'
TIMESTAMP '2003-11-30 15:04'

In some contexts, such as when putting row timestamps into CSV files, timestamps can be entered as a number of microseconds elapsed since midnight January 1, 1970. In this case, the numbers are treated as though they are relative to UTC, rather than local time. For example, if you use 1 as the timestamp, and your local time zone is Pacific Standard Time (eight hours behind UTC), the result is the following timestamp:

1969-12-31 16:00:00.000001

Interval Literals

Use either of two formats for an interval literal. The first form is similar to that of timestamp literals:

INTERVAL '{[D [day[s]]][ ][HH:MI[:SS[.FF]]]}'

Where:

D is the number of days. The space between the day specification and the hour and minute specification is optional.
HH:MI are numeric designations for hour and minute.
:SS is a designation for seconds, used only if the hour and minute are specified.
.FF is a designation for fractions of a second, using zero to six digits and only if seconds are specified.

The following sample illustrates this syntax:

INTERVAL '999 days 23:59:59.999999'

The alternative syntax for interval literals is:

{[D  day[s]][ ][HH hour[s]][ ][MI  minute[s]][ ][SS[.FF] second[s]][ ][NNN millisecond[s][ ][NNN  microsecond[s>

All components of the interval are optional. Here is an example:

4 minutes 5.6 seconds

Both forms of interval literals require that the values in each component be in the proper range. For example, you will get an error if you enter 61 minutes; you must enter this value as 1 hour 1 minute.