Skip to main content
Previous sectionNext section

SECOND

A time function that returns the second for a datetime expression.

Synopsis

{fn SECOND(time-expression)}

Arguments

Argument Description
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.

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.PosixTime (an encoded 64-bit signed integer), or data type %Library.TimeStamp (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 date portion of the timestamp string is not validated; the year can be in the range 0001 through 9999.

The seconds (ss) portion should be an integer in the range from 0 through 59. There is, however, no range checking for user-supplied values. Numbers greater than 59, negative numbers, and fractions are returned as specified. Leading zeros are optional on input. Leading and trailing zeros are suppressed on output.

SECOND returns 0 seconds when the seconds portion is '0', '00', or a nonnumeric value. Zero seconds is also returned if an ODBC date with no time expression is supplied, if the seconds portion of the time expression is omitted entirely ('hh', 'hh:mm', 'hh:mm:', or 'hh::'), or the time expression is invalid.

The same time information can be returned using DATEPART or DATENAME.

This function can also be invoked from ObjectScript using the SECOND() method call:

$SYSTEM.SQL.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.

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 Seconds_Given
Copy code to clipboard
SELECT {fn SECOND(67538)} AS Seconds_Given
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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
Copy code to clipboard

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')})
Copy code to clipboard

The following Embedded SQL example shows that the SECOND function recognizes the TimeSeparator character specified for the locale:

  DO ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator",".")
  &sql(SELECT {fn SECOND('2018-02-16 18.45.38')} INTO :a)
  WRITE "seconds=",a
Copy code to clipboard

See Also