Date, Time, and Timestamp Data Types

Using standard InterSystems SQL date, time functions, you can define date, time, and timestamp data types. You can also convert between dates and timestamps. For example, you can use CURRENT_DATE or CURRENT_TIMESTAMP as input to a field defined with that data type, or use DATEADD, DATEDIFF, DATENAME, or DATEPART to manipulate date values stored with this data type.

This table shows how date, time, and timestamp data type classes map to SQL types. The data type classes use this type when performing calculations in SQL. When creating a custom data type class, you can use these mappings to determine which SQL type to specify in the SqlCategory keyword of your class definition. For example:

Class MyApp.MyDateDT [ ClassType = DataType, SQLCategory = DATE ]
{
    // class members
}

Data Type Class	Corresponding SQL Type	Notes
%Library.DateOpens in a new tab classes Any property or column that has a logical value of +$HOROLOG (the date portion of $HOROLOG)	DATE	By default, the DATE and the corresponding %Library.DateOpens in a new tab data types accept only positive integers, with 0 representing 1840-12-31. To support dates earlier than 1840-12-31 you must define a date field in the table with data type %Library.Date(MINVAL=-nnn), where the MINVAL is a negative number of days counting backwards from 1840-12-31 to a maximum of -672045 (0001-01-01). %Library.DateOpens in a new tab can store a date value as an unsigned or negative integer in the range -672045 to 2980013. Date values can be input as follows: Logical mode accepts +HOROLOG integer values, such as 65619 (August 28, 2020). Display mode uses the DisplayToLogical() conversion method. It accepts a date in the display format for the current locale, for example ‘8/28/2020’. It also accepts a logical date value (a +HOROLOG integer value). ODBC mode uses the OdbcToLogical() conversion method. It accepts a date in ODBC standard format, for example ‘2020–08–28’. It also accepts a logical date value (a +HOROLOG integer value).
%Library.TimeOpens in a new tab classes Any class that has a logical value of $PIECE($HOROLOG,",",2), that is, the time portion of $HOROLOG	TIME	%Library.TimeOpens in a new tab stores a time value as an unsigned integer in the range 0 through 86399 (a count of seconds since midnight). Time values can be input as follows: Logical mode accepts $PIECE($HOROLOG,”,”,2) integer values, such as 84444 (23:27:24). Display mode uses the DisplayToLogical() conversion method. It accepts a time in the display format for the current locale, for example ‘23:27:24’. ODBC mode uses the OdbcToLogical() conversion method. It accepts a time in ODBC standard format, for example ‘23:27:24’. It also accepts a logical time value (an integer in the range 0 through 86399). TIME supports fractional seconds, so this data type can also be used for HH:MI:SS.FF to a user-specified number of fractional digits of precision (F), up to a maximum of 9. To support fractional seconds set the PRECISION parameter. For example, TIME(0) (%Time(PRECISION=0)) rounds to the nearest second; TIME(2) (%Time(PRECISION=2)) rounds (or zero-fills) to two fractional digits of precision. If the supplied data also specifies a precision (for example, CURRENT_TIME(3)), the fractional digits stored are as follows: If TIME specifies no precision and the data specifies a precision, use the precision of the data. If TIME specifies no precision and the data specifies no precision, use the system-wide configured time precision. If TIME specifies a precision and the data specifies no precision, use the system-wide configured time precision as the data precision. If TIME specifies a precision and the data precision is less than the TIME precision, use the data precision. If TIME specifies a precision and the data precision is greater than the TIME precision, use the TIME precision. SQL metadata reports fractional digits of time precision as “scale”; it uses the word “precision” for the overall length of the data. A field using the TIME data type reports precision and scale metadata as follows: TIME(0) (%Time(PRECISION=0)) has a metadata precision of 8 (nn:nn:nn) and a scale of 0. TIME(2) (%Time(PRECISION=2)) has a metadata precision of 11 (nn:nn:nn.ff) and a scale of 2. TIME (%Time or %Time(PRECISION="") take their fractional seconds of precision from the supplied data, and therefore have a metadata precision of 18 and an undefined scale. For details on returning data type, precision and scale metadata, refer to Select-item Metadata.
%Library.TimeStampOpens in a new tab classes Any class that has a logical value of YYYY-MM-DD HH:MI:SS.FF	TIMESTAMP	%Library.TimeStampOpens in a new tab derives its maximum precision from the system platform’s precision, up to a maximum of 9 fractional second digits, while %Library.PosixTimeOpens in a new tab has a maximum precision of 6 digits. Therefore, %Library.TimeStampOpens in a new tab may be more precise than %Library.PosixTimeOpens in a new tab on some platforms. %Library.TimeStampOpens in a new tab normalization automatically truncates input values with more than 9 digits of precision to 9 fractional second digits. Note: %Library.DateTimeOpens in a new tab is a subclass of %Library.TimeStampOpens in a new tab. It defines a type parameter named DATEFORMAT and it overrides the DisplayToLogical() and OdbcToLogical() methods to handle imprecise datetime input that TSQL applications are accustomed to.
%MV.Date classes Any class that has a logical date value of $HOROLOG-46385, which is the expression used to convert an ObjectScript date to a MultiValue date.	MVDATE	This data type is supported only for MultiValue compatbility.
Any data type that does not fit into any of the preceding logical values	DATE	When defining this class, define a LogicalToDate() method to convert logical date values to %Library.DateOpens in a new tab logical values, and a DateToLogical() method that performs the reverse operation.
%Library.PosixTimeOpens in a new tab classes Any user-defined data type class that has an encoded signed 64-bit integer logical value	POSIXTIME	%PosixTime is an encoded timestamp calculated from the number of seconds (and fractional seconds) since 1970–01–01 00:00:00. Timestamps after that date are represented by a positive %PosixTime value, timestamps before that date are represented by a negative %PosixTime value. %PosixTime supports a maximum of 6 digits of precision for fractional seconds. The earliest date supported by %PosixTime is 0001-01-01 00:00:00, which has a logical value of -6979664624441081856. The last date supported is 9999-12-31 23:59:59.999999, which has a logical value of 1406323805406846975. Because a %PosixTime value is always represented by a encoded 64-bit integer, it can always be unambiguously differentiated from a %Date or %TimeStamp value. For example, the %PosixTime value for 1970–01–01 00:00:00 is 1152921504606846976, the %PosixTime value for 2017–01–01 00:00:00 is 1154404733406846976, and the %PosixTime value for 1969–12–01 00:00:00 is -6917531706041081856. %PosixTime is preferable to %TimeStamp, because it takes up less disk space and memory than the %TimeStamp data type and provides better performance than %TimeStamp. You can integrate %PosixTime and %TimeStamp values by using the ODBC display mode: Logical mode values for %PosixTime and %TimeStamp data types are completely different: %PosixTime is a signed integer, %TimeStamp is a string containing an ODBC-format timestamp. Display mode: %PosixTime display uses the current locale time and date format parameters (for example, 02/22/2018 08:14:11); %TimeStamp displays as an ODBC-format timestamp. ODBC mode: both %PosixTime and %TimeStamp display as an ODBC-format timestamp. The number of fractional digits of precision may differ. You can convert %TimeStamp values to %PosixTime using the TO_POSIXTIME function or the TOPOSIXTIME()Opens in a new tab method. You can use the IsValid()Opens in a new tab method to determine if a numeric value is a valid %PosixTime value.
Any time data type that does not fit into any of the preceding logical values	TIME	When creating this class, define a LogicalToTime() method to convert logical time values to %Library.TimeOpens in a new tab logical values, and a TimeToLogical() method that performs the reverse operation.
Any timestamp data type that does not fit into any of the preceding logical values	TIMESTAMP	When defining this class, define a LogicalToTimeStamp() method to convert logical timestamp values to %Library.TimeStampOpens in a new tab logical values, and a TimeStampToLogical() method that performs the reverse operation.

You can compare POSIXTIME to DATE or TIMESTAMP values using =, <>, >, or < operators. Refer to Overview of Predicates for further details.

When comparing FMTIMESTAMP category values with DATE category values, InterSystems IRIS does not strip the time from the FMTIMESTAMP value before comparing it to the DATE. This is identical behavior to comparing TIMESTAMP with DATE values, and comparing TIMESTAMP with MVDATE values. It is also compatible with how other SQL vendors compare timestamps and dates. This means a comparison of a FMTIMESTAMP 320110202.12 and DATE 62124 are equal when compared using the SQL equality (=) operator. Applications must convert the FMTIMESTAMP value to a DATE or FMDATE value to compare only the date portions of the values.

Dates Prior to December 31, 1840

A date is commonly represented by the DATE data type or the TIMESTAMP data type.

The DATE data type stores a date in $HOROLOG format, as a positive integer count of days from the arbitrary starting date of December 31, 1840. By default, dates can only be represented by a positive integer (MINVAL=0), which corresponds to the date December 31, 1840. However, you can change the %Library.DateOpens in a new tab MINVAL type parameter to enable storage of dates prior to December 31, 1840. By setting MINVAL to a negative number, you can store dates prior to December 31, 1840 as negative integers. The earliest allowed MINVAL value is -672045. This corresponds to January 1 of Year 1 (CE). DATE data type cannot represent BCE (also known as BC) dates.

The TIMESTAMP data type defaults to 1840–12–31 00:00:00 as the earliest allowed timestamp. However, you can change the MINVAL parameter to define a field or property that can store dates prior to December 31, 1840. For example, MyTS %Library.TimeStamp(MINVAL='1492-01-01 00:00:00'). The earliest allowed MINVAL value is 0001–01–01 00:00:00. This corresponds to January 1 of Year 1 (CE). The %TimeStamp data type cannot represent BCE (also known as BC) dates.

Note:

Be aware that these date counts do not take into account changes in date caused by the Gregorian calendar reform (enacted October 15, 1582, but not adopted in Britain and its colonies until 1752).

You can redefine the minimum date for your locale as follows:

  SET oldMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
  IF oldMinDate=0 {
    DO ##class(%SYS.NLS.Format).SetFormatItem("DATEMINIMUM",-672045)
    SET newMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
    WRITE "Changed earliest date to ",newMinDate
    }
  ELSE { WRITE "Earliest date was already reset to ",oldMinDate}

The above example sets the MINVAL for your locale to the earliest permitted date (1/1/01). For more details on configuring dates based on your locale, see Configuring National Language Support (NLS).

Note:

InterSystems IRIS does not support using Julian dates with negative logical DATE values (%Library.Date values with MINVAL<0). Thus, these MINVAL<0 values are not compatible with the Julian date format returned by the TO_CHAR function.

Strings