PostgreSQL given date interval initial time calculation

1. Function Description:

Date_trunc: Intercept a given time (timestamp,date) to get the initial time of the specified precision (time, day, month, year)

2. General Time

Date_trunc (' Hour ', TIMESTAMP ' 2018-08-16 20:38:40 ')

Result:2018-08-16 20:00:00

Date_trunc (' Day ', TIMESTAMP ' 2018-08-16 20:38:40 ')

Result:2018-08-16 00:00:00

Date_trunc (' month ', TIMESTAMP ' 2018-08-16 20:38:40 ')

Result:2018-08-01 00:00:00

Date_trunc (' Year ', TIMESTAMP ' 2018-08-16 20:38:40 ')

Result:2018-01-01 00:00:00

3. Special Needs:

The first day of all months of the year for a given time period, the last day, the first day of the next month

--Result:month_first_day, Month_end_day, Next_month

Select Date (ZZ) as Month_first_day, date (zz + interval ' 1 month '-interval ' 1 day ') as Month_end_day, date (zz + interval ' 1 month ') as Next_month

From Generate_series ("Date_trunc" (' Year ', To_date (' 20180510 ', ' YYYYMMDD ')), Date_trunc (' Year ', To_date (' 201905 ', ' YYYYMMDD ')), ' 1 month ') as TT (ZZ);

SQL results:

Month_first_day | Month_end_day | Next_month

-----------------+---------------+------------

2018-01-01 | 2018-01-31 | 2018-02-01

2018-02-01 | 2018-02-28 | 2018-03-01

2018-03-01 | 2018-03-31 | 2018-04-01

2018-04-01 | 2018-04-30 | 2018-05-01

2018-05-01 | 2018-05-31 | 2018-06-01

2018-06-01 | 2018-06-30 | 2018-07-01

2018-07-01 | 2018-07-31 | 2018-08-01

2018-08-01 | 2018-08-31 | 2018-09-01

2018-09-01 | 2018-09-30 | 2018-10-01

2018-10-01 | 2018-10-31 | 2018-11-01

2018-11-01 | 2018-11-30 | 2018-12-01

2018-12-01 | 2018-12-31 | 2019-01-01

2019-01-01 | 2019-01-31 | 2019-02-01

(rows)

Find the initial value of the hour, day, month, and year of the specified time

--Result:dtrunc_hour, Dtrunc_day, Dtrunc_month, dtrunc_year

SELECT date_trunc (' Hour ', TIMESTAMP ' 2018-08-16 20:38:40 ') as Dtrunc_hour, Date_trunc (' Day ', TIMESTAMP ' 2018-08-16 20:38 : + ') as Dtrunc_day,date_trunc (' month ', TIMESTAMP ' 2018-08-16 20:38:40 ') as Dtrunc_month,date_trunc (' year ', TIMESTAMP ' 2018-08-16 20:38:40 ') as dtrunc_year;

SQL results:

Dtrunc_hour |    Dtrunc_day |     Dtrunc_month | Dtrunc_year

---------------------+---------------------+---------------------+---------------------

2018-08-16 20:00:00 | 2018-08-16 00:00:00 | 2018-08-01 00:00:00 | 2018-01-01 00:00:00

(1 row)

postgres=#

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1946257

PostgreSQL for a given date interval initial time calculation