PostgreSQL - DateTime functions and operators
This presentation is an overview of PostgreSQL date and time functions and operators covering current_time(), age(timestamp, timestamp), age(timestamp) , clock_timestamp(), current_date(), current_time(), current_timestamp,date_part(), date_trunc(text, timestamp) and more date and time functions with examples.
Transcript
PostgreSQL : DateTime functions and operators
age(timestamp, timestamp)
The age() function subtract arguments, producing a "symbolic" result that uses years and months.
Return Type : interval
Example : SELECT age(timestamp '2003-05-15', timestamp '1973-07-19');
Output :
age ---------------------------------- 29 years 9 mons 27 days (1 row)
age(timestamp)
The age() function is used to subtract age from current_date (at midnight).
Return Type : interval
Example : SELECT age(timestamp '1973-07-19');
Output :
age -------------------------------- 41 years 1 mon 4 days (1 row)
clock_timestamp()
The clock_timestamp() function shows current date and time (changes during statement execution).
Return Type : timestamp with time zone.
Example : SELECT clock_timestamp();
Output :
clock_timestamp ------------------------------------------- 2014-08-27 17:25:03.121+05:30 (1 row)
N.B. The outputs depending upon the current date and time.
current_date()
The current_date() function returns the current date.
Return Type : date
Example : SELECT current_date;
Output :
date ----------------- 2014-08-27 (1 row)
N.B. The outputs depending upon the current date and time.
current_time()
The current_time() function returns the current time.
Return Type : time with time zone
Example : SELECT current_time;
Output :
timetz --------------------------- 17:38:47.982+05:30 (1 row)
N.B. The outputs depending upon the current time zone.
current_timestamp
The current_timestamp function returns the current date and time according to the timestamp.
Return Type : timestamp with time zone
Example : SELECT current_timestamp;
Output :
now ------------------------------------------- 2014-08-27 17:39:22.139+05:30 (1 row)
N.B. The outputs depending upon the current date and time.
date_part(text, timestamp)
The date_part() function is used to get subfield (equivalent to extract).
Return Type : double precision
Example : SELECT date_part('hour', timestamp '2002-09-17 19:27:45');
Output :
date_part ----------------- 19 (1 row)
date_part(text, interval)
The date_part() function is used to get subfield (equivalent to extract).
Return Type : double precision
Example : SELECT date_part('month', interval '3 years 7 months');
Output :
date_part ---------------- 7 (1 row)
date_trunc(text, timestamp)
The date_trunc() function is used to truncate to specified precision;
Return Type : timestamp
Example : SELECT date_trunc('hour', timestamp '2002-09-17 19:27:45');
Output :
date_trunc ----------------------------- 2002-09-17 19:00:00 (1 row)
extract(field from timestamp)
The date_trunc() function is used to get subfield.
Return Type : double precision
Example : SELECT extract(hour from timestamp '2002-09-17 19:27:45');
Output :
date_part ----------------- 19 (1 row)
extract(field from interval)
The date_trunc() function is used to get subfield.
Return Type : double precision
Example : SELECT extract(month from interval '3 years 7 months');
Output :
date_part --------------- 7 (1 row)
isfinite(date)
The isfinite() function is used to get test for finite date (not +/-infinity).
Return Type : boolean
Example : SELECT isfinite(date '2002-09-17');
Output :
isfinite ---------- t (1 row)
isfinite(timestamp)
The isfinite() function is used to get test for finite date (not +/-infinity).
Return Type : boolean
Example : SELECT isfinite(timestamp '2002-09-17 19:27:45');
Output :
isfinite -------------- t (1 row)
isfinite(interval)
This function is used to test for finite interval.
Return Type : boolean
Example : SELECT isfinite(interval '7 hours');
Output :
isfinite ---------- t (1 row)
justify_days(interval)
This function is used to adjust interval so 30-day time periods are represented as months.
Return Type : interval
Example : SELECT justify_days(interval '47 days');
Output :
justify_days ---------------------- 1 mon 17 days (1 row)
justify_hours(interval)
This function is used to adjust interval so 24-hour time periods are represented as days.
Return Type : interval
Example : SELECT justify_hours(interval '32 hours');
Output :
justify_hours ---------------------- 1 day 08:00:00 (1 row)
justify_interval(interval)
This function is used to adjust interval using justify_days and justify_hours, with additional sign adjustments.
Return Type : interval
Example : SELECT justify_interval(interval '1 mon -1 hour');
Output :
justify_interval ------------------------- 29 days 23:00:00 (1 row)
localtime
This function is used to get current time of day.
Return Type : time
Example : SELECT localtime;
Output :
time --------------------------- 17:45:55.808 (1 row)
N.B. The outputs depending upon the current time.
localtimestamp
This function is used to get current date and time (start of current transaction).
Return Type : timestamp
Example : SELECT localtimestamp;
Output :
timestamp ------------------------------------- 2014-08-27 17:46:28.295 (1 row)
N.B. The outputs depending upon the current date and time.
now()
This function is used to get current date and time (start of current transaction).
Return Type : timestamp with time zone
Example : SELECT now();
Output :
now ---------------------------------------------- 2014-08-27 17:47:04.447+05:30 (1 row)
N.B. The outputs depending upon the current date and time.
statement_timestamp()
This function is used to get current date and time (start of current transaction).
Return Type : timestamp with time zone
Example : SELECT statement_timestamp();
Output :
statement_timestamp ------------------------------------------------ 2014-08-27 17:48:42.232+05:30 (1 row)
N.B. The outputs depending upon the current date.
timeofday()
This function is used to get current date and time (like clock_timestamp, but as a text string).
Return Type : text
Example : SELECT timeofday();
Output :
timeofday ----------------------------------------------------- Wed Aug 27 17:49:11.447000 2014 IST (1 row)
N.B. The outputs depending upon the current date.
transaction_timestamp()
This function is used to get current date and time (start of current transaction
Return Type : timestamp with time zone
Example : SELECT transaction_timestamp();
Output :
transaction_timestamp ----------------------------------------------- 2014-08-27 17:49:45.895+05:30 (1 row)
N.B. The outputs depending upon the current date.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics