Timestamp Formats

Timestamps appear not just in the TimeStamp columns, but also in columns such as the StartTime, EndTime, and LastModified columns. You can use the date or time, or both the date and time in a SQL statement that contains a timestamp. Valid date and time formats are as follows:

  • System short date and time.
  • SQL date and time.
  • ODBC date and time.

The time format for system short timestamps is the same as the time format defined in the Windows Control Panel.

When entering a query you should use a period as the decimal separator to separate seconds from milliseconds or microseconds.

When using the SQL date and time, you should always use the English abbreviations for the desired month.

If you enter only a start time, the end time is assumed to be now. For example, if you enter starttime > yesterday in a WHERE clause, the end time for the query is now, even if you previously set an end time.

If you enter only an end time, the start time is December 31, 1969, 19:00:00.001. If you use this as the start time, you can overload the Historian server and the provider. For example, if you use timestamp < now, you might cause an overload.

The following examples show how each time format appears in a sample SET statement.

Example 1: Use the System Short Date and Time

SET starttime='02/01/2002 11:00:00'

Example 2: Use the SQL Date and Time

SET starttime='14-sep-2001 11:00:00'

Example 3: Use the ODBC Date and Time

SET starttime={ts '2002-06-20 15:34:08'}

Example 4: Set the Start Time to 4 AM Today

SET starttime='04:00:00'

Example 5: Set the Start Time in Milliseconds

SET starttime='7/12/2011 12:03:16.183'

Example 6: Set the Start Time in Microseconds

SET starttime='7/12/2011 12:03:16.178439'