SECOND (SQL)
Synopsis
{fn SECOND(time-expression)}
Description
SECOND returns an integer from 0 to 59, and may return fractional seconds as well. The seconds are calculated for a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string (with no time value), or a timestamp.
A time-expression timestamp can be either data type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).
To change the default time format, use the SET OPTION command.
You must supply either a timestamp string (yyyy-mm-dd hh:mm:ss) or a $HOROLOG string. A $HOROLOG string may be a full datetime string (63274,37279) or only the time integer portion of $HOROLOG (37279). You cannot supply a time string (hh:mm:ss); this always returns 0, regardless of the actual number of seconds.
The time portion of the datetime string must be a valid time. Otherwise, an SQLCODE -400 error <ILLEGAL VALUE> is generated. The seconds (ss) portion must be an integer in the range from 0 through 59. Leading zeros are optional on input; leading zeros are suppressed on output.
The date portion of the datetime string is not validated.
SECOND returns 0 seconds when the seconds portion is '0' or '00'. Zero seconds is also returned if an ODBC date with no time expression is supplied, or if the seconds portion of the time expression is omitted entirely ('hh', 'hh:mm', 'hh:mm:', or 'hh::').
The same time information can be returned using DATEPART or DATENAME.
This function can also be invoked from ObjectScript using the SECOND()Opens in a new tab method call:
$SYSTEM.SQL.Functions.SECOND(time-expression)
Fractional Seconds
SECOND returns fractions of a second if supplied in time-expression. Trailing zeros are truncated. If no fractional seconds are specified (for example: 38.00) the decimal separator is also truncated.
The standard InterSystems IRIS internal representation of time values ($HOROLOG) does not support fractional seconds. Timestamps do support fractional seconds.
The following SQL functions support fractional seconds: SECOND, CURRENT_TIMESTAMP, DATENAME, DATEPART, and GETDATE. CURTIME, CURRENT_TIME, and NOW do not support fractional seconds.
The SQL SET OPTION statement permits you to set the default precision (number of decimal digits) for fractional seconds.
The ObjectScript $ZTIMESTAMP special variable can be used to represent fractional seconds. The ObjectScript functions $ZDATETIME, $ZDATETIMEH, $ZTIME, and $ZTIMEH support fractional seconds.
Arguments
time-expression
An expression that is the name of a column, the result of another scalar function, or a string or numeric literal. It must resolve either to a timestamp string or a $HOROLOG string, where the underlying data type can be represented as %Time, %TimeStamp, or %PosixTime.
Examples
The following examples both return the number 38 because it is the thirty-eighth second of the time expression:
SELECT {fn SECOND('2018-02-16 18:45:38')} AS ODBCSeconds
SELECT {fn SECOND(67538)} AS HorologSeconds
The following example returns .9 seconds. The leading and trailing zeros are truncated:
SELECT {fn SECOND('2018-02-16 18:45:00.9000')} AS Seconds_Given
The following example returns 0 seconds because the seconds portion of the datetime string has been omitted:
SELECT {fn SECOND('2018-02-16 18:45')} AS Seconds_Given
The following example returns 0 seconds because the time expression has been omitted from the datetime string:
SELECT {fn SECOND('2018-02-16')} AS Seconds_Given
The following examples all return the seconds portion of the current time, in whole seconds:
SELECT {fn SECOND(CURRENT_TIME)} AS Sec_CurrentT,
{fn SECOND({fn CURTIME()})} AS Sec_CurT,
{fn SECOND({fn NOW()})} AS Sec_Now,
{fn SECOND($HOROLOG)} AS Sec_Horolog,
{fn SECOND($ZTIMESTAMP)} AS Sec_ZTS
The following example shows that leading zeros are suppressed. The first SECOND function returns a length 2, the others return a length of 1. An omitted time is considered to be 0 seconds, which has a length of 1:
SELECT LENGTH({fn SECOND('2018-02-15 11:45:22')}),
LENGTH({fn SECOND('2018-02-15 03:05:06')}),
LENGTH({fn SECOND('2018-02-15 3:5:6')}),
LENGTH({fn SECOND('2018-02-15')})
The following example shows that the SECOND function recognizes the TimeSeparator character specified for the locale:
SELECT {fn SECOND('2018-02-16 18.45.38')}
See Also
-
SQL concepts: Data Type, Date and Time Constructs
-
SQL functions: HOUR, MINUTE, CURRENT_TIME, CURTIME, NOW, DATEPART, DATENAME
-
ObjectScript function: $ZTIME
-
ObjectScript special variables: $HOROLOG, $ZTIMESTAMP