SQL Reference

MIDNIGHT_SECONDS

>>-MIDNIGHT_SECONDS--(--expression--)--------------------------><
 

The schema is SYSFUN.

Returns an integer value in the range 0 to 86 400 representing the number of seconds between midnight and the time value specified in the argument.

The argument must be a time, timestamp, or a valid character string representation of a time or timestamp that is neither a CLOB nor a LONG VARCHAR.

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

Example:

• Find the number of seconds between midnight and 00:10:10, and midnight and 13:10:10.

  VALUES (MIDNIGHT_SECONDS('00:10:10'), MIDNIGHT_SECONDS('13:10:10'))
  

  This example returns the following:

  1           2          
  ----------- -----------
          610       47410
  

  Since a minute is 60 seconds, there are 610 seconds between midnight and the specified time. The same follows for the second example. There are 3600 seconds in an hour, and 60 seconds in a minute, resulting in 47410 seconds between the specified time and midnight.

• Find the number of seconds between midnight and 24:00:00, and midnight and 00:00:00.

  VALUES (MIDNIGHT_SECONDS('24:00:00'), MIDNIGHT_SECONDS('00:00:00'))
  

  This example returns the following:

  1           2          
  ----------- -----------
        86400               0
  

  Note that these two values represent the same point in time, but return different MIDNIGHT_SECONDS values.