How to get yesterday date starts at 00:00:00 in Vertica SQL

Question

How to get yesterday date starts at 00:00:00 in Vertica SQL?

Normally in SQL Server it will be like this SELECT DATEADD(Day, -1, DATEDIFF(Day, 0, GetDate())) 2021-01-31 00:00:00.000

How about in Vertica?

Answer 1

I would clearly go closer to the ANSI standard. There is an ANSI reserved word CURRENT_DATE, which is an expression that returns today's date (without the time). Many DBMSs support it (I actually think SQL Server, too); and I would stick to that - or to CURRENT_TIMESTAMP if I need the timestamp. "Never use DBMS specifics if you can avoid them".

In Vertica, you can subtract integers from or add integers to dates. You can't use SQL-Server's peculiar DATEADD(), but you can use the more standard (in other DBMSs) TIMESTAMPADD() which works like DATEADD() and returns a timestamp.

For completeness's sake: If you have a result timestamp with something else than '00:00:00' as the time element, use TRUNC(<timestamp>) to truncate the value and get a timestamp with the time at zero.

SELECT 
  CURRENT_DATE   AS ANSI_CURRENT_DATE
, CURRENT_DATE-1 AS yesterday_midnight_minus
, TIMESTAMPADD(DAY,-1,CURRENT_DATE) AS tsadd
;
-- out  ANSI_CURRENT_DATE | yesterday_midnight_minus |        tsadd        
-- out -------------------+--------------------------+---------------------
-- out  2021-02-01        | 2021-01-31               | 2021-01-31 00:00:00