Skip to main content

TO_POSIXTIME (SQL)

A date/time function that converts a formatted date string to a %PosixTime timestamp.

Synopsis

TO_POSIXTIME(date_string[,format])

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.

Arguments

date-string

A string expression to be converted to a %PosixTime timestamp. This expression may contain a date value, a time value, or a date and time value.

format

An optional date and time format string corresponding to date_string. If omitted, defaults to DD MON YYYY HH:MI:SS

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.

Format

A format is a string of one or more format elements specified according to the following rules:

  • Format elements are not case-sensitive.

  • Almost any sequence or number of format elements is permitted.

  • Format strings separate their elements with non-alphanumeric separator characters (for example, a space, slash, or hyphen) that match the separator characters in the date_string. These separator characters do not appear in the output string, which uses standard timestamp separators: hyphens for date values, colons for time values, and a period (when required) for fractional seconds. This use of separator characters does not depend on the DateSeparator defined for your NLS locale.

  • The following date format strings do not require separator characters: MMDDYYYY, DDMMYYYY, YYYYMMDDHHMISS, YYYYMMDDHHMI, YYYYMMDDHH, YYYYMMDD, YYYYDDMM, HHMISS, and HHMI. The incomplete date format YYYYMM is also supported, and assume a DD value of 01. Note that in these cases leading zeros must be provided for all elements (such as MM and DD), with the exception of the final element.

  • Characters in format that are not valid format elements are ignored.

Format Elements

The following table lists the valid date format elements for the format argument:

Element Meaning
DD Two-digit day of month (01-31). Leading zeros are not required, unless format contains no date separator characters.
MM

Two-digit month number (01-12; 01 = January). Leading zeros are not required, unless format contains no date separator characters.

In Japanese and Chinese, a month number consists of a numeric value followed by the ideogram for “month”.

MON Abbreviated name of month, as specified by the MonthAbbr property in the current locale. By default, in English this is the first three letters of the month name. In other locales, month abbreviations may be more than three letters long and/or may not consist of the first letters of the month name. A period character is not permitted. Not case-sensitive.
MONTH Full name of the month, as specified by the MonthName property in the current locale. Not case-sensitive.
YYYY Four-digit year.
YY Last two digits of the year. The first 2 digits of a YY 2-digit year default to 19.
RR / RRRR Two-digit year to four-digit year conversion. (See below.)
DDD Day of the year. The number of days since January 1. (See below.)
HH Hour, specified as either 01–12 or 00–23, depending on whether a meridian indicator (AM or PM) is specified. Can be specified as HH12 or HH24.
MI Minute, specified as 00–59.
SS Second, specified as 00–59.
FF Fractions of a second. FF indicates that one or more fractional digits are provided; date_string can specify any number of fractional digits. TO_POSIXTIME returns exactly six digits of precision, regardless of the precision supplied in date_string.
AM / PM Meridian indicator, specifies a 12–hour clock. (See below.)
A.M. / P.M. Meridian indicator (with periods), specifies a 12–hour clock. (See below.)

A TO_POSIXTIME format can also include a D (day of week number), DY (day of week abbreviation), or DAY (day of week name) element to match the input date_string. However, these format elements are not validated or used to determine the return value. For further details on these format elements, refer to TO_CHAR.

Two-Digit Year Conversion (RR and RRRR formats)

The RR format provides two-digit to four-digit year conversion. TO_POSIXTIME performs this conversion using the default date format (dformat -1), which uses the YearOption property of current locale, as described in $ZDATETIME.

Day of the Year (DDD format)

You can use DDD to convert the day of the year (number of days elapsed since January 1) to an actual date. The format string DDD YYYY must be paired with a corresponding date_string consisting of an integer number of days and a four-digit year. (Two-digit years must be specified as RR (not YY) when used with DDD.) The format string DDD defaults to the current year. The number of elapsed days must be a positive integer in the range 1 through 365 (366 if YYYY is a leap year). The four-digit year must be within the standard InterSystems IRIS date range: 1841 through 9999. (If you omit the year, it defaults to the current year.) The DDD and year (YYYY, RRRR, or RR) format elements can be specified in any order; a separator character between them is mandatory; this separator can be a blank space. The following example shows this use of Day of the Year:

SELECT TO_POSIXTIME('2018:160','YYYY:DDD')

If a format string contains both a DD and a DDD element, the DDD element is dominant. This is shown in the following example, which returns 2008-02-29 00:00:00 (not 2008-12-31 00:00:00):

SELECT TO_POSIXTIME('2018-12-31-60','YYYY-MM-DD-DDD')

TO_POSIXTIME permits you to return a date expression corresponding to a day of the year. TO_CHAR permits you to return the day of the year corresponding to a date expression.

Dates Before 1970

TO_POSIXTIME represents a date before January 1, 1970 as a negative number. %PosixTime cannot represent dates before January 1, 0001 or after December 31, 9999. Attempted to input such a date results in an SQLCODE -400 error. The TO_DATE function provides a Julian date format to represent BCE dates before January 1, 0001. Julian date conversion converts a seven-digit internal positive integer value (a Julian day count) to a display-format or ODBC-format date. Time values are not supported for Julian dates.

12-Hour Clock Time

A %PosixTime timestamp always represents time using a 24-hour clock. A date_string may represent time using a 12-hour clock or a 24-hour clock. TO_POSIXTIME assumes a 24-hour clock, unless one of the following applies:

  • The date_string time value is followed by 'am' or 'pm' (with no periods). These meridian indicators are not case-sensitive, and may be appended to the time value, or be separated from it by one or more spaces.

  • The format follows the time format with an 'a.m.' or 'p.m.' element (either one), separated from the time format by one or more spaces. For example: DD MON YYYY HH:MI:SS.FF P.M. This format supports 12-hour clock date_string values such as 2:23pm, 2:23:54.6pm, 2:23:54 pm, 2:23:54 p.m., and 2:23:54 (assumed to be AM). Meridian indicators are not case-sensitive. When using a meridian indicator with periods, it must be separated from the time value by one or more spaces.

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.

See Also

FeedbackOpens in a new tab