Commonly used Vertica Date Functions and Examples

  • Post author:
  • Post last modified:April 29, 2019
  • Post category:Vertica
  • Reading time:7 mins read

Date functions are very important when you are working with various sources. For example, extract day from the date type and store it in database tables. In this article, we will check commonly used Vertica date functions and some examples.

Vertica Date Functions

Vertica date functions are similar to other PostgreSQL systems such as Netezza. There are some date functions that are native to Vertica database. Most of the real-world applications use date functions to manipulate date values. Date types are highly formatted and are very complicated. Each date function will work on century, year, month, day, hour, minute, and second values. You can also use type conversion functions to convert one type to date type values.

Related readings:

Commonly used Vertica Date Functions

Below are some of commonly used Vertica date functions:

Date Function Description
now() Returns the current system date and time.
current_time Return current system date.
current_timestamp Returns the current system date and time.
current_date() Returns the date on which the current transaction started.
sysdate() Return current system date in timestamp type.
add_months(date,n) Adds the specified number of months to a date and returns the sum as a DATE.
age_in_months( [ date1,] date2 ) Returns the difference in months between two dates, expressed as an integer.
age_in_years( [ date1,] date2 ) Returns the difference in years between two dates, expressed as an integer.
date_part(units,date) Extracts the subfield represented by units from the date/time value, interval, or duration specified for date. Units can be day, month, year, century, epoch, hour, minute, etc.
date(value) Converts the input value to a date type.
date_trunc(units,date) Truncates the date specified for date to the precision specified by units. Units can be day, month, year, century, epoch, hour, minute, etc.
datediff( datepart, start, end ) Returns the time span between two dates, in the intervals specified. Datepart could be year, month, day, quarter, week, hour, minute, seconds, milliseconds, etc.
day(value) Return day of the month as an integer.
month(value) Return month value as an integer.
year(value) Return year value as an integer.
hour(value) Return hour value as an integer.
minute(value) Return value of minute as an integer.
second(value) Return value of second as an integer.
quarter(date) Returns calendar quarter for given date.
week(value) Return calendar week for given date.
extract(units FROM date) This function is equivalent to date_part(unit,n).
getdate() Returns the current statement’s start date and time as a TIMESTAMP value.
last_day(date) Return last day of the month of the specified date column or value
months_between(date1, date2) Returns number of month between two dates.
next_day(date,weekday) Returns the date of the weekday that follows a particular date.

Vertica Date Functions Examples

Below are some of examples that uses Netezza Date functions:

Get current date and timestamp

select now(); 
 NOW 
--------------------- 
 2016-08-31 15:48:24 
(1 row) 

select current_date; 
 DATE 
------------ 
 2016-08-31 
(1 row) 

select current_timestamp; 
 TIMESTAMP 
--------------------- 
 2016-08-31 15:48:46 
(1 row)

Add 1 month to current date

select add_months(now(),1); 
 ADD_MONTHS 
--------------------- 
 2016-09-30 16:12:05 
(1 row) 

select date(current_date + interval '1 day'); 
 DATE 
------------ 
 2016-09-30 
(1 row)

Subtract 1 month from current date

select add_months(now(),-1); 
 ADD_MONTHS 
--------------------- 
 2016-07-31 16:12:52 
(1 row) 

select date(current_date - interval '1 day'); 
 DATE 
------------ 
 2016-08-30 
(1 row)

Extract date part

select extract (day from current_date); 
 DATE_PART 
----------- 
 31 
(1 row) 

select date_part ('day', current_date); 
 DATE_PART 
----------- 
 31 
(1 row)

Get first day and last day of the previous month

select last_day(now() - interval '2 month') + interval '1 day' as first_day; 
 FIRST_DAY 
--------------------- 
 2016-07-01 00:00:00 
(1 row) 

select last_day(now() - interval '2 month') as last_day; 
 LAST_DAY 
------------ 
 2016-06-30 
(1 row)

Add hours to current date

select now() + interval '1 hour' as "1hour_later"; 
 1hour_later 
--------------------- 
 2016-08-31 17:23:44 
(1 row)

Get first day of the given date

select date_trunc('month', current_timestamp); 
 DATE_TRUNC 
--------------------- 
 2016-08-01 00:00:00 
(1 row)

Get last day of the given month

select date_trunc('month', current_timestamp)-1; 
 ?COLUMN? 
------------ 
 2016-07-31 
(1 row)

Convert date to integer

select to_number(now(),'99999999'); 
 TO_NUMBER 
----------- 
 -2016083 
(1 row)

Hope this helps 🙂

Tags: ,