How many seconds since midnight?

[Deleted User] · July 2018

Jim Knicely authored this post.

There are many reasons you might want to know the number of seconds that have passed since midnight (i.e., event logging). You could combine several Vertica built-in date manipulation functions to calculate the number of seconds since midnight, but the easiest way is to use the MIDNIGHT_SECONDS function!

Example:

dbadmin=> SELECT current_timestamp "CURRENT TIMESTAMP",
dbadmin->        DATEDIFF(second, TRUNC(current_timestamp), current_timestamp) "Seconds from Midnight",
dbadmin->        (current_timestamp - TRUNC(current_timestamp))::INTERVAL SECOND::INT "Seconds from Midnight",
dbadmin->        MIDNIGHT_SECONDS(current_timestamp) "Seconds from Midnight (An easier way)";
       CURRENT TIMESTAMP       | Seconds from Midnight | Seconds from Midnight | Seconds from Midnight (An easier way)
-------------------------------+-----------------------+-----------------------+---------------------------------------
 2018-07-18 10:59:42.164302-04 |                 39582 |                 39582 |                                 39582
(1 row)

The MIDNIGHT_SECONDS function is particularly useful to Vampires:

dbadmin=> SELECT current_timestamp "CURRENT TIMESTAMP",
dbadmin->        MIDNIGHT_SECONDS(current_timestamp) "Seconds from Midnight",
dbadmin->        CASE WHEN MIDNIGHT_SECONDS(current_timestamp) >= 21600 /* 6:00 AM */ THEN 'Get back to your coffin!' ELSE 'Still got time to feast!' END "ALERT";
       CURRENT TIMESTAMP       | Seconds from Midnight |          ALERT
-------------------------------+-----------------------+--------------------------
 2018-07-18 10:59:42.164302-04 |                 39582 | Get back to your coffin!
(1 row)

Have fun!