TIMESTAMP

Edit online

The TIMESTAMP function returns a TIMESTAMP WITHOUT TIME ZONE value from its argument or arguments.



The rules for the arguments depend on whether the second argument is specified.

  • If only one argument is specified: The argument must be an expression that returns a value of one of the following built-in data types: a date, a timestamp, a character string, a graphic string, or a binary string. If expression-1 is a character or graphic string, it must not be a CLOB or DBCLOB and it must have one of the following values:
    • A valid string representation of a date or timestamp with an actual length that is not greater than 255 bytes. A time zone in a string representation of a timestamp is ignored.
    • A character string or graphic string with an actual length of 8 that is assumed to be a IBM® Z Store Clock value.
    • A character string with an actual length of 13 that is assumed to be a result from the GENERATE_UNIQUE function.
    • A character string or graphic string with an actual length of 14 that represents a valid date and time in the form yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds.
    If expression-1 is a binary string, it must not be a BLOB and its value must be one of the following:
    • A binary string with an actual length of 8 bytes that is assumed to be a IBM Z Store Clock value.
    • A binary string with an actual length of 16 bytes that is assumed to be a IBM Z Store Clock extended value.
  • If both arguments are specified:,
    • If the data type of the second argument is not an integer:

      The first argument must be an expression that returns a value of one of the following built-in data types: a date, a character string, or a graphic string. The second argument must be an expression that returns a value of one of the following built-in data types: a time, a character string, or a graphic string. A character string or graphic string must be a valid string representation of a time.

      If expression-1 is a character string or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a date with an actual length that is not greater than 255 bytes. If expression-2 is a character string or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a time with an actual length that is not greater than 255 bytes.

    • If the data type of the second argument is integer:

      The first argument must be an expression that returns a value of one of the following built-in data types: a timestamp, a date, a character string, or a graphic string. The second argument must be an integer constant in the range 0 to 12 that represents the timestamp precision.

      If expression-1 is a character string or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a timestamp or a date with an actual length that is not greater than 255 bytes.

      If expression-1 is a binary string, it must not be a BLOB, and its value must conform to the rules for when only one argument is specified. The second argument must be an integer constant in the range 0 to 12 that represents the timestamp precision.

The result of the function is a TIMESTAMP WITHOUT TIME ZONE value.

The timestamp precision and other rules depend on whether the second argument is specified:

If both arguments are specified and the second argument is not an integer:
The result is a TIMESTAMP(6) WITHOUT TIME ZONE value with the date that is specified by the first argument and the time that is specified by the second argument. The fractional seconds part of the timestamp is zero.
If both arguments are specified and the second argument is an integer:
The result is a TIMESTAMP WITHOUT TIME ZONE value with the precision that is specified in the second argument.
If only one argument is specified and it is a TIMESTAMP (p) WITHOUT TIME ZONE:
The result is that TIMESTAMP (p) WITHOUT TIME ZONE value.
If only one argument is specified and it is a TIMESTAMP(p) WITH TIME ZONE:
The result is the argument value, cast to TIMESTAMP(p) WITHOUT TIME ZONE. The value is the local timestamp, not UTC.
If only one argument is specified and it is a date:
The result is that date with an assumed time of midnight that is cast to TIMESTAMP(0) WITHOUT TIME ZONE.
If only one argument is specified and it is a character or graphic string:
The result is the TIMESTAMP(6) WITHOUT TIME ZONE value that is represented by that string extended with any missing time information. If the argument is a string of length 14, the TIMESTAMP has a fractional seconds part of zero. The string value must not contain a specification of time zone.
If only one argument is specified and it is a binary string:
The result is the TIMESTAMP(6) WITHOUT TIME ZONE value that is represented by that string. If the year value in the resulting timestamp is greater than 9999 an error is returned (SQLSTATE 22007, SQLCODE -180).

If the arguments include only date information, the time information in the result value is all zeros.

If an argument is a string with a CCSID that is not the same as the corresponding default CCSID at the server, the string is first converted to that CCSID.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID. If both arguments are specified and their encoding schemes are different, the result CCSID is the appropriate CCSID of the application encoding scheme.

Notes

Specifying an LRSN as an argument:
When a 6-byte LRSN is used as the argument to the TIMESTAMP function, it must be left justified and padded on the right to a total length of 8 bytes. When a 10-byte LRSN is used, it must be left justified and padded on the right to a total length of 16 bytes.
Syntax alternatives:
If only one argument is specified, the CAST specification should be used for maximal portability.

TIMESTAMP_TZ is a similar function.

Examples

Example:
The following example selects the timestamp from the column UPDATED_TS in the table EMPLOYEE:

SELECT TIMESTAMP(UPDATED_TS) FROM EMPL_COMP

The above example returns 2019-11-26 05:39:59.454707.