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字段转换成天 然后相加
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhfgkcig
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01