Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be learning the most commonly used PostgreSQL date functions that allow you to effectively change date and time values.
AGE():It shows the day, month and year differences between two dates.
1 | select age(current_date,’1991.08.20′) |
CURRENT DATE:It gives the date information at the time it was run.
1 | select CURRENT_DATE |
CURRENT_TIME:It gives the time information at the time it was run.
1 | select CURRENT_TIME |
DATE_PART():It is used to print the day, month and year parameters of the specified date.
1 2 3 4 5 | SELECT date_part(‘year’, TIMESTAMP ‘2020-11-23 20:38:40’); SELECT date_part(‘month’, TIMESTAMP ‘2020-11-23 20:38:40’); SELECT date_part(‘day’, TIMESTAMP ‘2020-11-23 20:38:40’); |
Finding the difference between two times with the date_part method.
1 | SELECT DATE_PART(‘year’, ‘2012-01-01’::date) – DATE_PART(‘year’, ‘2011-10-02’::date); |
EXTRACT():It is similar to the DATE_PART() function.
1 2 3 4 5 | SELECT EXTRACT(year FROM TIMESTAMP ‘2020-11-23 20:38:40’); SELECT EXTRACT (month FROM TIMESTAMP ‘2020-11-23 20:38:40’); SELECT EXTRACT(day FROM TIMESTAMP ‘2020-11-23 20:38:40’); |
ISFINITE():Date formats etc. used for testing.
1 | SELECT isfinite(timestamp ‘2020-11-23 20:38:40’); |
DATE_TRUNC():It is used to reset the specified date as year, month and day.
1 | SELECT date_trunc(‘YEAR’, TIMESTAMP ‘2020-11-23 20:38:40’); |
