Timestamp

Timestamp Functions

Represents a point in time as a date and, optionally, a time value.

Timestamp values have a precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds, and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum.

There is no timezone information stored in timestamp; they are all assumed to be in the UTC timezone.

The number of bytes used to store a timestamp depends on its precision (the on-disk storage varies between 5 and 9 bytes).

This datatype is specified as a string in the following format:

"<yyyy>-<mm>-<dd>[T<HH>:<mm>:<ss>[.<SS>]]"

where:

You can return the current time as a timestamp using the current_time() function. See Time Functions for details.

Timestamp Functions

The following functions can be used with the timestamp datatype:

  • year(<timestamp>)

    Returns the year for the given timestamp. The returned value is in the range -6383 to 9999. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • month(<timestamp>)

    Returns the month for the given timestamp. The returned value is in the range 1 to 12. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • day(<timestamp>)

    Returns the day for the given timestamp. The returned value is in the range 1 to 31. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • hour(<timestamp>)

    Returns the hour for the given timestamp. The returned value is in the range 0 to 23. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • minute(<timestamp>)

    Returns the minute for the given timestamp. The returned value is in the range 0 to 59. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • second(<timestamp>)

    Returns the second for the given timestamp. The returned value is in the range 0 to 59. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • millisecond(<timestamp>)

    Returns the millisecond for the given timestamp. The returned value is in the range 0 to 999. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • microsecond(<timestamp>)

    Returns the microsecond for the given timestamp. The returned value is in the range 0 to 999999. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • nanosecond(<timestamp>)

    Returns the nanosecond for the given timestamp. The returned value is in the range 0 to 999999999. If the <timestamp> argument is NULL or empty, the result is also NULL or empty.

  • week(<timestamp>)

    Returns the week number within the year. Weeks start on a Sunday, and the first week in the year has a minimum of 1 day. The value returned is in the range 1 to 54. If the argument is NULL or empty, the result is also NULL or empty.

  • isoweek(<timestamp>)

    Returns the week number within the year based on IS0-8601. Weeks start on Monday, and the first week has a minimum of 4 days. The value returned is in the range 0 to 53. If the argument is NULL or empty, the result is also NULL or empty.

  • extract(<unit> from <expr>)

    Extracts temporal fields from a timestamp field. <expr> must return a timestamp. <unit> must be one of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, WEEK, or ISOWEEK.

See Working with Timestamps for an example of using these functions.