Time Literals

Time literals are used to specify timestamps and intervals.

Timestamp Literals

The syntax of a Timestamp literal is as follows:

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.

Note that one or more blank spaces must be used 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 and is as follows:

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 the hour and minute.
:SS is the seconds, used only when hours and minutes are specified.
.FF is fractions of a second, using zero to six digits and only if seconds are specified.

Here is an example of this syntax form:

INTERVAL '999 days 23:59:59.999999'

The second syntax form of Interval literals is as follows:

{W week[s]][ ][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, but you must include at least one (not counting spaces). Here is an example:

2 days 3 hours 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.