MINUTE (SQL)
Synopsis
{fn MINUTE(time-expression)}
Description
MINUTE returns an integer specifying the minutes for a given time or datetime value. Minutes are calculated for a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, 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.
Note that you can supply a time integer (number of elapsed seconds), but not a time string (hh:mm:ss). You must supply a datetime string (yyyy-mm-dd hh:mm:ss).
The time portion of the datetime string must be a valid time. Otherwise, an SQLCODE -400 error <ILLEGAL VALUE> is generated. The minutes (mm) portion must be an integer in the range from 0 through 59. Leading zeros are optional on input; leading zeros are suppressed on output. You can omit the seconds (:ss) portion of a datetime string and still return the minutes portion.
The date portion of the datetime string is not validated.
MINUTE returns zero minutes when the minutes portion is '0' or '00’. Zero minutes is also returned if no time expression is supplied, or the minutes portion of the time expression is omitted entirely ('hh', 'hh:', 'hh::', or 'hh::ss').
The same time information can be returned using DATEPART or DATENAME.
This function can also be invoked from ObjectScript using the MINUTE()Opens in a new tab method call:
$SYSTEM.SQL.Functions.MINUTE(time-expression)
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 datetime string or a time integer, where the underlying data type can be represented as %Time, %TimeStamp, or %PosixTime.
Examples
The following examples both return the number 45 because it is the forty-fifth minute of the time expression in the datetime string:
SELECT {fn MINUTE('2018-02-16 18:45:38')} AS ODBCMinutes
SELECT {fn MINUTE(67538)} AS HorologMinutes
The following example also returns 45. As shown here, the seconds portion of the time value can be omitted:
SELECT {fn MINUTE('2018-02-16 18:45')} AS Minutes_Given
The following example returns 0 minutes because the time expression has been omitted from the datetime string:
SELECT {fn MINUTE('2018-02-16')} AS Minutes_Given
The following examples all return the minutes portion of the current time:
SELECT {fn MINUTE(CURRENT_TIME)} AS Min_CurrentT,
{fn MINUTE({fn CURTIME()})} AS Min_CurT,
{fn MINUTE({fn NOW()})} AS Min_Now,
{fn MINUTE($HOROLOG)} AS Min_Horolog,
{fn MINUTE($ZTIMESTAMP)} AS Min_ZTS
The following example shows that leading zeros are suppressed. The first MINUTE function returns a length 2, the others return a length of 1. An omitted time is considered to be 0 minutes, which has a length of 1:
SELECT LENGTH({fn MINUTE('2018-02-22 11:45:00')}),
LENGTH({fn MINUTE('2018-02-22 03:05:00')}),
LENGTH({fn MINUTE('2018-02-22 3:5:0')}),
LENGTH({fn MINUTE('2018-02-22')})
The following Embedded SQL example shows that the MINUTE function recognizes the TimeSeparator character specified for the locale:
DO ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator",".")
&sql(SELECT {fn MINUTE('2018-02-22 18.45.38')} INTO :a)
QUIT:(SQLCODE '= 0)
WRITE "minutes=",a
See Also
-
SQL concepts: Data Type, Date and Time Constructs
-
SQL functions: HOUR, SECOND, CURRENT_TIME, CURTIME, NOW, DATEPART, DATENAME
-
ObjectScript function: $ZTIME
-
ObjectScript special variables: $HOROLOG, $ZTIMESTAMP