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’); |
