TIMESTAMP Data Type

The TIMESTAMP data type represents date and time of day data.

Value range:

TIMESTAMP values range from January 1, -8192 through December 31, 8191, to an accuracy of one hundred nanoseconds (one ten-thousandth of a millisecond).

Date-only and Time-only Timestamps

TIMESTAMP values can be restricted to represent date data only or time of day data only.
As explained in the Remarks section below, a TIMESTAMP value that has been restricted to represent date data only or time of day data only will be formatted to display date elements only (year, month, and day) or time of day elements only (hour, minute, second, millisecond, and nanosecond).
TIMESTAMP values can be restricted to date-only or time-only timestamps in different ways.
Some input formats return TIMESTAMP input record fields whose values represent only dates or times of day. For example, the "date" and "time" fields of the IISW3C input format have values representing only dates and times of day, respectively.
TIMESTAMP constants can also be entered as date-only or time-only timestamp values, depending on the Timestamp Format Specifiers used.
In addition, the TO_DATE, TO_TIME, SYSTEM_DATE, and SYSTEM_TIME functions all return TIMESTAMP values representing dates or times of day only.
For more information, refer to the Remarks section below.

Remarks:

• TIMESTAMP values are formatted and parsed using Timestamp Format Specifiers. Timestamp format specifiers are strings that use special characters to describe date and/or time elements in a string representation of a timestamp. For more information, refer to the Timestamp Format Specifiers reference.
• Although the distinction between date-only or time-only TIMESTAMP values and full TIMESTAMP values is often transparent to the user, date-only or time-only values behave differently than full TIMESTAMP values in the following circumstances:
  • Comparison operators in expressions: When comparing a date-only TIMESTAMP value with another TIMESTAMP value, the time of day data of the date-only value is assumed to be time zero. Similarly, when comparing a time-only TIMESTAMP value with another TIMESTAMP value, the date data of the time-only value is assumed to be January 1, year 0.
  • Formatting TIMESTAMP values: whenever a date-only or time-only TIMESTAMP value is formatted to a STRING value by either explicitly using the TO_STRING function or as implicitly done by an output format, the resulting STRING will only contain the date or time of day data, and the non-applicable Timestamp Format Specifiers will be ignored.
    As an example, the following query uses the TO_STRING function with date and time of day format specifiers to format the "time" field of the IISW3C input format:

    SELECT TO_STRING(time, 'yyyy-MM-dd hh:mm:ss')
    FROM <1>
    

    Since the values of the "time" field are time-only TIMESTAMP values, the resulting STRING values will be formatted according to the time of day format specifiers only, and the date format specifiers will be ignored:

    18:48:04
    18:48:27
    18:48:27
    18:48:29
    

• Values of type TIMESTAMP can also be used to represent time intervals, for example with the ADD and SUB functions.
  Since the origin of time in the Log Parser SQL-Like language is January 1, year 0, time intervals should be expressed as timestamps relative to this origin of time.
  For example, a time interval of one day should be specified as January 2, year 0, i.e. 24 hours after the origin of time.
  The following example query selects all the event log records that have been written in the past 2 days:
  

  SELECT *
  FROM SYSTEM
  WHERE TimeWritten > TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP('0000-01-03', 'yyyy-MM-dd') ) )
  

• TIMESTAMP values do not carry information on the timezone the timestamp is relative to.
  When working with TIMESTAMP fields generated by an input format, users should be aware of the timezone these fields are relative to, and handle their values accordingly.
  For example, values of the "TimeGenerated" field of the EVT Input Format are relative to the local timezone. If Universal Time Coordinates (UTC) are desired, the TO_UTCTIME function should be used to convert these local timestamps to UTC timestamps.

Conversion Functions:

Other data types to TIMESTAMP data type:

• TO_TIMESTAMP

TIMESTAMP data type to other data types:

• TO_INT
• TO_REAL
• TO_STRING

Full TIMESTAMP values to date-only TIMESTAMP values:

• TO_DATE

Full TIMESTAMP values to time-only TIMESTAMP values:

• TO_TIME

Date-only and time-only TIMESTAMP values to full TIMESTAMP values:

• TO_TIMESTAMP

Local timezone TIMESTAMP values to UTC TIMESTAMP values:

• TO_UTCTIME

UTC TIMESTAMP values to local timezone TIMESTAMP values:

• TO_LOCALTIME

See also:

© 2004 Microsoft Corporation. All rights reserved.