MIDNIGHT_SECONDS

The MIDNIGHT_SECONDS function returns an integer value that is greater than or equal to 0 and less than or equal to 86 400 representing the number of seconds between midnight and the time value specified in the argument.

Read syntax diagramSkip visual syntax diagramMIDNIGHT_SECONDS (expression)
expression
An expression that returns a value of one of the following built-in data types: a date, a time, a timestamp, a character string, or a graphic string. Its value must be a valid string representation of a time or timestamp. For the valid formats of string representations of times and timestamps, see String representations of datetime values.

If the argument is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).

The result of the function is large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Examples

  • 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).
  • Find the number of seconds between midnight and 24:00:00, and midnight and 00:00:00.
      SELECT MIDNIGHT_SECONDS('24:00:00'), MIDNIGHT_SECONDS('00:00:00')
        FROM SYSIBM.SYSDUMMY1
    This example returns 86400 and 0. Although these two values represent the same point in time, different values are returned.