Chapter 95 SQL function MINUTE
The minute time function that returns a date-time expression.
The outline
{fn MINUTE(time-expression)}
Copy the code
parameter
time-expression
– As an expression for a column name, the result of another scalar function, or a string or numeric literal. It must be resolved as a date-time string or a time integer, where the underlying data type can be expressed as%Time
,%TimeStamp
或%PosixTime
.
describe
MINUTE Returns an integer specifying the number of minutes for a given time or date-time value. Calculates minutes for $HOROLOG or $ZTIMESTAMP values, odBC-formatted date strings, or time stamps.
The time expression TimeStamp can be of data type % library.posixtime (encoded 64-bit signed integer) or of data type % library.timestamp (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 (the number of seconds that have passed), but not a time string (hh:mm:ss). The date and time string (YYYY-MM-DD hh:mm: SS) must be provided.
Date time The time part of the string must be a valid time. Otherwise, an SQLCODE -400 error
will be generated. The minute (mm) portion must be an integer ranging from 0 to 59. Leading zeros are optional at input; Leading zeros are suppressed on the output. You can omit the seconds (:ss) portion of the date-time string, but still return the minutes portion.
Date time The date portion of the string is not validated.
When the MINUTE part is 0 or 00, MINUTE returns zero minutes. Zero minutes is also returned if no time expression is provided, or if the minute part of the time expression is omitted entirely (‘hh’, ‘hh:’, ‘hh:’, or ‘hh::ss’).
You can use DATEPART or DATENAME to return the same time information.
This function can also be called from ObjectScript using the MINUTE() method call:
$SYSTEM.SQL.Functions.MINUTE(time-expression)
Copy the code
The sample
The following examples all return the number 45 because it is the 45th minute of the time expression in the Datetime string:
SELECT {fn MINUTE('the 2018-02-16 18:45:38')} AS ODBCMinutes
45
Copy the code
SELECT {fn MINUTE(67538)} AS HorologMinutes
45
Copy the code
The following example also returns 45. Here, the second part of the time value can be omitted:
SELECT {fn MINUTE('the 2018-02-16 a little')} AS Minutes_Given
45
Copy the code
The following example returns 0 minutes because the time expression is omitted from the date-time string:
SELECT {fn MINUTE('2018-02-16')} AS Minutes_Given
0
Copy the code
The following examples all return the minute 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
27 27 27 27 27
Copy the code
The following example shows leading zeros suppressed. The first MINUTE function returns length 2, and the others return length 1. The omitted time is considered to be 0 minutes and its length is 1:
SELECT LENGTH({fn MINUTE('the 2018-02-22 11:45:00')}),
LENGTH({fn MINUTE('the 2018-02-22 03:05:00')}),
LENGTH({fn MINUTE('the 2018-02-22 3:5:0')}),
LENGTH({fn MINUTE('2018-02-22')})
2 1 1 1
Copy the code
The following embedded SQL example shows the TimeSeparator character recognized by the MINUTE function for the locale specified:
/// d ##class(PHA.TEST.SQLFunction).Minute()
ClassMethod Minute(a)
{
d ##class(%SYS.NLS.Format).SetFormatItem("TimeSeparator& ", ". ")sql(
SELECT {fn MINUTE('the 2018-02-22 18.45.38')}
INTO :a)
w "minutes=",a
}
Copy the code
DHC-APP>d ##class(PHA.TEST.SQLFunction).Minute(a)minutes=45
Copy the code