PostgreSQL日期和时间操作大全

1. 官网帮忙文档

        日期时间函数网址

1.2 函数使用方法

Pattern	Description	Remark
HH	hour of day (01–12)
HH12	hour of day (01–12)
HH24	hour of day (00–23)
MI	minute (00–59)
SS	second (00–59)
MS	millisecond (000–999)
US	microsecond (000000–999999)
FF1	tenth of second (0–9)
FF2	hundredth of second (00–99)
FF3	millisecond (000–999)
FF4	tenth of a millisecond (0000–9999)
FF5	hundredth of a millisecond (00000–99999)
FF6	microsecond (000000–999999)
SSSS, SSSSS	seconds past midnight (0–86399)
AM, am, PM or pm	meridiem indicator (without periods)
A.M., a.m., P.M. or p.m.	meridiem indicator (with periods)
Y,YYY	year (4 or more digits) with comma
YYYY	year (4 or more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week-numbering year (4 or more digits)
IYY	last 3 digits of ISO 8601 week-numbering year
IY	last 2 digits of ISO 8601 week-numbering year
I	last digit of ISO 8601 week-numbering year
BC, bc, AD or ad	era indicator (without periods)
B.C., b.c., A.D. or a.d.	era indicator (with periods)
MONTH	full upper case month name (blank-padded to 9 chars)
Month	full capitalized month name (blank-padded to 9 chars)
month	full lower case month name (blank-padded to 9 chars)
MON	abbreviated upper case month name (3 chars in English, localized lengths vary)
Mon	abbreviated capitalized month name (3 chars in English, localized lengths vary)
mon	abbreviated lower case month name (3 chars in English, localized lengths vary)
MM	month number (01–12)
DAY	full upper case day name (blank-padded to 9 chars)
Day	full capitalized day name (blank-padded to 9 chars)
day	full lower case day name (blank-padded to 9 chars)
DY	abbreviated upper case day name (3 chars in English, localized lengths vary)	星期几的英文，前3个字母，3个字母都大写
Dy	abbreviated capitalized day name (3 chars in English, localized lengths vary)	星期几的英文，前3个字母，首字线大写，后2个字母小写
dy	abbreviated lower case day name (3 chars in English, localized lengths vary)	星期几的英文，前3个字母，3个字母都为小写
DDD	day of year (001–366)
IDDD	day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)
DD	day of month (01–31)
D	day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	week of month (1–5) (the first week starts on the first day of the month)	月里的周别，1号为周别开始的日期，7天为一个周别，来计算，例如：2023年1月份的第1周的日期为（1月1号至1月7号）第2周的日期为(1月8号至1月14号）
WW	week number of year (1–53) (the first week starts on the first day of the year)	年里的周别：1月1号开始计算，7天为周别，依次来计算，例如2022年第1周为(1月1号至1月7号），第2周为（1月8号至1月5号）
IW	week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)	ISO 8601周编号年份的周编号（01–53；一年的第一个星期四在第1周）：看一年里第一个星期四落在的周，为本年的第一周
CC	century (2 digits) (the twenty-first century starts on 2001-01-01)
J	Julian Date (integer days since November 24, 4714 BC at local midnight; see Section B.7)
Q	quarter
RM	month in upper case Roman numerals (I–XII; I=January)
rm	month in lower case Roman numerals (i–xii; i=January)
TZ	upper case time-zone abbreviation (only supported in to_char)
tz	lower case time-zone abbreviation (only supported in to_char)
TZH	time-zone hours
TZM	time-zone minutes
OF	time-zone offset from UTC (only supported in to_char)

---

2.操作实例 

select now(); --当前时间 2021-04-21 11:36:56

SELECT EXTRACT (year FROM now()); --年 2021
SELECT EXTRACT (quarter FROM now()); --季 02
SELECT EXTRACT (month FROM now()); --月 04
SELECT EXTRACT (day FROM now()); --月中天 21
SELECT EXTRACT (week FROM now()); --周 16
SELECT EXTRACT (isodow FROM now()); --周中天 3
SELECT EXTRACT (hour FROM now()); --時 11
SELECT EXTRACT (minute FROM now()); --分 36
SELECT EXTRACT (second FROM now()); --秒 56.909161

2.1 周别和星期操作大全

2.1.1 求周别的函数

select  to_char(to_date('2023-01-02','YYYY-MM-DD'),'W');
select  to_char(to_date('2023-01-02','YYYY-MM-DD'),'WW');
select  to_char(to_date('2023-01-02','YYYY-MM-DD'),'IW');

---

3. 计算小时数实例[包括小时数的加减运算]

        将数字型转化成interval,进行运算，得到日期时间型，例如 SELECT now()::timestamp + (qty || ' day')::interval FROM table --把col字段转换成天 然后相加

--当前日期0点，返回值为时间截
select date_trunc('day', now())
--当时时间的小时数，整点，返回值为时间截
select  date_trunc('hour', now())
--当时日期8点整，返回值为时间截
select date_trunc('day', now()) + interval '8 h' 
--当前时间，时分秒
select current_time;
--关于日期计算，转化成 interval 
SELECT now()::timestamp + '1 year';  --当前时间加1年
SELECT now()::timestamp + '1 month';  --当前时间加一个月
SELECT now()::timestamp + '1 day';  --当前时间加一天
SELECT now()::timestamp + '1 hour';  --当前时间加一个小时
SELECT now()::timestamp + '1 min';  --当前时间加一分钟
SELECT now()::timestamp + '1 sec';  --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';  --加1年1月1天1时1分1秒
SELECT now()::timestamp + (qty || ' day')::interval FROM table --把col字段转换成天 然后相加