Netezza Date Functions and Examples

  • Post author:
  • Post last modified:April 29, 2019
  • Post category:Netezza
  • Reading time:6 mins read

This article is about detailed descriptions and examples of the standard Netezza date functions that you can use to manipulate date columns in the Netezza SQL and Netezza stored procedure.

Netezza Date Functions

In the real word scenarios many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. Each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same.

Netezza provides many way to handle the date data types. Below are the some of the commonly used Netezza date functions.

Netezza Date Functions

Below are the Netezza supported 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
add_months(date,n) Returns the date that corresponds to date plus the number of months indicated by the integer n. +ve n determines the addition and -ve n represent date substraction
age(ts,ts) and age(ts) Returns the interval between two timestamps. Used to calculate the exact age. If you specify a single argument, the function returns the interval between the current time and the specified timestamp
date_part(units,col) Extracts the subfield represented by units from the date/time value, interval, or duration specified for col. Units can be day, month, year etc
date_trunc(units,date) Truncates the date specified for date to the precision specified by units.
extract(units FROM col) This function is equivalent to date_part(units,col)
last_day(date) Return last day of the month of the specified date column or value
months_between(d1,d2) Returns number of month between two dates
next_day(date,weekday) Returns the date of the weekday that follows a particular date.
overlaps(a,b,c,d) Determines whether two time intervals overlap
duration_add(a,b) Returns the duration when second argument is added to the first
duration_subtract(a,b) Returns the duration when secod argument is substracted from the first
timeofday() Verbose version of timestamp
timezone(timestamp from_tz to_tz) Converts the input timestamp value in the from_tz timezone to a timestamp in the to_tz timezone value

Netezza Date Functions Examples

Below are some of examples that uses Netezza Date functions:

Get current date and timestamp

TRAINING.ADMIN(ADMIN)=> select now(); 
 NOW 
--------------------- 
 2016-08-31 15:48:24 
(1 row) 

TRAINING.ADMIN(ADMIN)=> select current_date; 
 DATE 
------------ 
 2016-08-31 
(1 row) 

TRAINING.ADMIN(ADMIN)=> select current_timestamp; 
 TIMESTAMP 
--------------------- 
 2016-08-31 15:48:46 
(1 row)

Add 1 month to current date

TRAINING.ADMIN(ADMIN)=> select add_months(now(),1); 
 ADD_MONTHS 
--------------------- 
 2016-09-30 16:12:05 
(1 row) 

TRAINING.ADMIN(ADMIN)=> select date(current_date + interval '1 day'); 
 DATE 
------------ 
 2016-09-30 
(1 row)

Subtract 1 month from current date

TRAINING.ADMIN(ADMIN)=> select add_months(now(),-1); 
 ADD_MONTHS 
--------------------- 
 2016-07-31 16:12:52 
(1 row) 

TRAINING.ADMIN(ADMIN)=> select date(current_date - interval '1 day'); 
 DATE 
------------ 
 2016-08-30 
(1 row)

Extract date part

TRAINING.ADMIN(ADMIN)=> select extract (day from current_date); 
 DATE_PART 
----------- 
 31 
(1 row) 

TRAINING.ADMIN(ADMIN)=> select date_part ('day', current_date); 
 DATE_PART 
----------- 
 31 
(1 row)

Get first day and last day of the previous month

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

TRAINING.ADMIN(ADMIN)=> select last_day(now() - interval '2 month') as last_day; 
 LAST_DAY 
------------ 
 2016-06-30 
(1 row)

Add hours to current date

TRAINING.ADMIN(ADMIN)=> 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

TRAINING.ADMIN(ADMIN)=> select date_trunc('month', current_timestamp); 
 DATE_TRUNC 
--------------------- 
 2016-08-01 00:00:00 
(1 row)

Get last day of the given month

TRAINING.ADMIN(ADMIN)=> select date_trunc('month', current_timestamp)-1; 
 ?COLUMN? 
------------ 
 2016-07-31 
(1 row)

Convert date to integer

TRAINING.ADMIN(ADMIN)=> select to_number(now(),'99999999'); 
 TO_NUMBER 
----------- 
 -2016083 
(1 row)

Related Reading:

Tags: , , ,