Use postgresql to get the year, month and day of the current or a certain time period

Recently, I encountered the need to obtain a certain time, year, month and day. I will write an article for my own recording.

1. Get the current time

select now();                    
--return：2022-12-07 15:53:15.881711+08
 
select current_date;      
--return：2022-12-07
 
select current_timestamp;
--return：2022-12-07 15:56:28.729581+08
 
select current_time;
--return：15:56:50.155444+08

2. Get the first day of the current month

select date_trunc('month',current_date)::DATE;
--return：2022-12-01

3. Obtain the next day, next month, and previous year based on the current date

select (current_date + INTERVAL '1 day')::DATE;
--Get the next day based on the current date，return：2022-12-08
select date_trunc('month',(current_date + INTERVAL '1 month'))::DATE;
--Get the next month based on the current date，return：2023-01-01
select (current_date - INTERVAL '2 YEAR')::DATE;
--Get the previous year based on the current date，return：2020-12-07

If you want to get the time of the "week" cycle, just change day, month or year to week, and you can do it if you add s or not.

4. According to the content of the third step, we can also get the last day of the previous month/year

select (date_trunc('month',current_date) - interval'1 day')::DATE;
--Use firstdate_truncFunction gets the date of the first day of the month，Then go back one day，return：2022-11-30
select (date_trunc('year',current_date) - interval'1 day')::DATE;
--Same as above，Use firstdate_truncFunction gets the date of the first day of the year，Then go back one day，return：2021-12-31

Note here if we want to get the first day date or the last day date of the month before the previous year, that is, my current time is 2022-12-07, I want to get the first day of November 2021 or the last day, the writing method should be

select (date_trunc('year',current_date) - interval'2 month')::DATE;
--From the current date2022-12-07Get2021Year11The first day of the month，return：2021-11-01
select ((date_trunc('year',current_date) - interval'1 month')-INTERVAL '1 day')::DATE;
--From the current date2022-12-07Get2021Year11The date of the last day of the month，return：2021-11-30
--I don't know if there is an easier way，Hope to learn

5. According to the idea of step 4, we can obtain the date of the last day of the month

select (date_trunc('month',current_date) + interval'1 month - 1 day')::DATE;
--Use firstdate_truncFunction gets the date of the first day of the month，Then add another month，One more day at the end，return：2022-12-31

6. Although the first few steps are finally converted to date format, you can also intercept the hours, minutes and seconds of any date.

SELECT substr(to_char(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss'),12,8);
--return：16:19:34

Summarize

This is the end of this article about using postgresql to obtain the year, month and day of the current or a certain time period. For more relevant postgresql to obtain the year, month and day content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!