MIDNIGHT_SECONDS scalar function

The MIDNIGHT_SECONDS function returns an integer, in the range 0–86400, that represents the number of seconds between midnight and the time that is specified in the argument.

The schema is SYSIBM.

The argument must be an expression that returns a value of one of the following built-in data types: a time, a timestamp, a character string, or a graphic string. If expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a time or timestamp with an actual length of not greater than 255 bytes. For the valid formats of string representations of times and timestamps, see String representations of datetime values.

If expression is a timestamp with a time zone, or a valid string representation of a timestamp with a time zone, the result is determined from the UTC representation of the datetime value.

The result of the function is a large integer.

The result can be null; if the argument is null, the result is the null value.

Example 1: Find the number of seconds between midnight and 00:01:00, and midnight and 13:10:10. Assume that host variable XTIME1 has a value of '00:01:00', and that XTIME2 has a value of '13:10:10'.

SELECT MIDNIGHT_SECONDS(:XTIME1), MIDNIGHT_SECONDS(:XTIME2)
     FROM SYSIBM.SYSDUMMY1;

This example returns 60 and 47410. Because there are 60 seconds in a minute and 3600 seconds in an hour, 00:01:00 is 60 seconds after midnight ((60 * 1) + 0), and 13:10:10 is 47410 seconds ((3600 * 13) + (60 * 10) + 10).

Example 2: Find the number of seconds between midnight and 24:00:00 of a specified day, and between midnight and 00:00:00 of the following day.

SELECT MIDNIGHT_SECONDS('2019-05-20-24:00:00'), 
     MIDNIGHT_SECONDS('2019-05-21-00:00:00')
     FROM SYSIBM.SYSDUMMY1;

This example returns 86400 and 0. Although the two MIDNIGHT_SECONDS arguments represent the same point in time, different values are returned. End of change

Example 3: The following invocations of the MIDNIGHT_SECONDS function return the same result:

SELECT MIDNIGHT_SECONDS('2003-01-02-20.10.05.123456'), 
             MIDNIGHT_SECONDS('2003-01-02-12.10.05.123456-08:00'), 
		MIDNIGHT_SECONDS('2003-01-03-05.10.05.123456+09:00') 
		FROM SYSIBM.SYSDUMMY1;

For each invocation of the MIDNIGHT_SECONDS function in this SELECT statement, the result is 72605.

When the input argument contains a time zone, the result is determined from the UTC representation of the input value. The string representations of a timestamp with a time zone in the SELECT statement all have the same UTC representation: 2003-01-02-20.10.05.123456.