How many seconds since midnight?

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners
edited July 19 in Vertica Tips

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!

Sign In or Register to comment.