PostgreSQL initial time calculation for a given date interval

1. Function description:

Date_trunc: intercept a given time (TIMESTAMP,date) to obtain the initial time of a specified precision (hour, day, month, year)

two。 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, the last day, and the first day of next month of all months of the year for a given period of time

-- 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)), date_trunc ('year',to_date (' 201905)),'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

(13 rows)

Find out the initial values of hours, days, months and years at a specified time

-- Result: dtrunc_hour, dtrunc_day, dtrunc_month, dtrunc_year

SELECT date_trunc ('hour', TIMESTAMP' 2018-08-16 20 as dtrunc_hour 38) as dtrunc_hour, date_trunc ('day', TIMESTAMP' 2018-08-16 20 as dtrunc_hour 38 40') as dtrunc_day,date_trunc ('month', TIMESTAMP' 2018-08-16 20 20 as dtrunc_hour 38 40') as dtrunc_month,date_trunc ('year', TIMESTAMP' 2018-08-16 203840 date_trunc

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=#