The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

How many seconds since midnight?

edited July 2018 in Tips from the Team

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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.