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