A date/time function that converts a formatted date string
to a %PosixTime timestamp.
Description
The TO_POSIXTIME function converts date and time strings in
various formats to a %PosixTime timestamp, with data type %Library.PosixTimeOpens in a new tab. TO_POSIXTIME returns a %PosixTime timestamp as a calculated value based on the
number of elapsed seconds from the arbitrary starting point of 1970-01-01
00:00:00, encoded as a 64-bit signed integer. The actual number of
elapsed seconds (and fractional seconds) from this date is the Unix®timestamp, a numeric value.
InterSystems IRIS encodes the Unix® timestamp to generate the
%PosixTime timestamp. Because a %PosixTime timestamp value is encoded,
1970-01-01 00:00:00 is represented as 1152921504606846976. Dates prior
to 1970-01-01 00:00:00 have a negative integer value. Refer to the
%PosixTime data type for further details.
TO_POSIXTIME does not convert timezones;
a local date and time is converted to a local %PosixTime timestamp;
a UTC date and time is converted to a UTC %PosixTime timestamp.
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.
These limits correspond to the ODBC date format display limits. These
values can be further limited using the %Library.PosixTimeOpens in a new tab MINVAL and MAXVAL parameters. You can use the IsValid()Opens in a new tab method to determine if a numeric value is a valid
%PosixTime value.
A %PosixTime value always encodes a precision of 6 decimal digits
of fractional seconds. A date_string with fewer
digits of precision is zero-padded to 6 digits before %PosixTime conversion;
a date_string with more than 6 digits of precision
is truncated to 6 digits before %PosixTime conversion.
If date_string omits components of the timestamp, TO_POSIXTIME supplies the missing components. If both date_string and format omit the year,
yyyy defaults to the current year; if only date_string omits the year, it defaults to 00, which is expanded to a four-digit
year according to the year format element. If a
day or month value is omitted, dd defaults to 01; mm-dd defaults to
01-01. A missing time component defaults to 00. Fractional seconds
are supported, but must be explicitly specified; no fractional seconds
are provided by default.
TO_POSIXTIME supports conversion of two-digit
years to four digits. TO_POSIXTIME supports conversion
of 12-hour clock time to 24-hour clock time. It provides range validation
of date and time element values, including leap year validation. Range
validation violations generate an SQLCODE -400 error.
This function can also be invoked from ObjectScript using the TOPOSIXTIME()Opens in a new tab method call:
$SYSTEM.SQL.Functions.TOPOSIXTIME(date_string,format)
The TO_POSIXTIME function can be used in
data definition when supplying a default value to a field. For example:
CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
End_Year DATE DEFAULT TO_POSIXTIME('12-31-2018','MM-DD-YYYY') NOT NULL)
TO_POSIXTIME can be used with the CREATE TABLE or ALTER TABLE ADD COLUMN statements. Only a literal value for date_string can be used in this context. For further details, refer to the CREATE TABLE command.
%PosixTime Representation
%PosixTime encodes 6 digits of precision for fractional seconds,
regardless of the precision of the date_string.
The ODBC and Display modes truncate trailing zeros of precision.
Date and Time String
The date_string argument specifies a date
and time string literal. If you supply a date string with no time
component, TO_POSIXTIME supplies the time value
00:00:00. If you supply a time string with no date component, TO_POSIXTIME supplies the date of 01–01 (January
1) of the current year.
You can supply a date and time string of any kind for the input date_string. Each date_string character
must correspond to the format string, with the
following exceptions:
-
Leading zeros may be included or omitted (with the
exception a date_string without separator characters).
-
Years may be specified with two digits or four digits.
-
Month abbreviations (with format MON) must match the month abbreviation for that locale. For some
locales, a month abbreviation may not be the initial sequential characters
of the month name. Month abbreviations are not case-sensitive.
-
Month names (with format MONTH)
should be specified as full month names. However, TO_POSIXTIME does not require full month names with format MONTH; it accepts the initial character(s) of the full month name
and selects the first month in the month list that corresponds to
that initial letter sequence. Therefore, in English, “J”
= “January”, “Ju” = “June”, “Jul”
= “July”. All characters specified must match the sequential
characters of the full month name; characters beyond the full month
name are not checked. For example, “Fe”, “Febru”,
and “FebruaryLeap” are all valid values; “Febs”
is not a valid value. Month names are not case-sensitive.
-
Time values can be input with the time separator characters
defined for the locale. The output timestamp always represents the
time value with the ODBC standard time separator characters: colon
(:) and period (.)). An omitted time element defaults to zeroes.
Examples
The following Embedded SQL example converts the current local
datetime to a %PosixTime value. (Note that format uses “ff” to represent any number of fractional digits;
in this case, 3 digits of precision. %PosixTime encodes this as 6
digits of precision, supplying three trailing zeroes.) This example
then uses the %Posix LogicalToOdbc()Opens in a new tab method to display
this value as an ODBC timestamp, trimming trailing zeroes of precision:
SET tstime=$ZDATETIME($ZTIMESTAMP,3,1,3)
WRITE "local datetime in : ",tstime,!
&sql(SELECT
TO_POSIXTIME(:tstime,'yyyy-mm-dd hh:mi:ss.ff')
INTO :ptime)
IF SQLCODE=0 {
WRITE "Posix encoded datetime: ",ptime,!
SET ODBCout=##class(%PosixTime).LogicalToOdbc(ptime)
WRITE "local datetime out: ",ODBCout }
ELSE { WRITE "SQLCODE error:",SQLCODE }
The following example specifies date strings in various formats. The first one uses the default format, the others specify a format. All of these convert date_string to the timestamp value of 2018–06–29 00:00:00:
SELECT
TO_POSIXTIME('29 JUN 2018'),
TO_POSIXTIME('2018 Jun 29','YYYY MON DD'),
TO_POSIXTIME('JUNE 29, 2018','month dd, YYYY'),
TO_POSIXTIME('2018***06***29','YYYY***MM***DD'),
TO_POSIXTIME('06/29/2018','MM/DD/YYYY'),
TO_POSIXTIME('29/6/2018','DD/MM/YYYY')
The following example specifies the YYYYMM date format. It does
not require element separators. TO_POSIXTIME supplies
the missing day and time values:
SELECT TO_POSIXTIME('201806','YYYYMM')
This example returns the timestamp 2018–06–01 00:00:00.
The following example specifies just the HH:MI:SS.FF time format. TO_POSIXTIME supplies the missing date value. In each case, this example returns the date of 2018–01–01 (where 2018 is the current year):
SELECT TO_POSIXTIME('11:34','HH:MI:SS.FF'),
TO_POSIXTIME('11:34:22','HH:MI:SS.FF'),
TO_POSIXTIME('11:34:22.00','HH:MI:SS.FF'),
TO_POSIXTIME('11:34:22.7','HH:MI:SS.FF'),
TO_POSIXTIME('11:34:22.7000000','HH:MI:SS.FF')
Note that fractional seconds are passed through exactly as specified, with no padding or truncation.
