PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)匯總大全
更新時(shí)間：2023年09月28日 16:38:36 作者：一碗情深
PostgreSQL是一款簡(jiǎn)介而又性能強(qiáng)大的數(shù)據(jù)庫(kù)應(yīng)用程序,其在日期時(shí)間數(shù)據(jù)方面所支持的功能也都非常給力,這篇文章主要給大家介紹了關(guān)于PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)的相關(guān)資料,需要的朋友可以參考下
前言
本文基于 PostgreSQL 12.6 版本，不同版本的函數(shù)可能存在差異。
查看版本 psql --version。
查看時(shí)區(qū)
show timezone; --UTC select now(); -- 2023-07-24 09:22:48.589640 +00:00
視圖 pg_timezone_names 保存了所有可供選擇的時(shí)區(qū)
select * from pg_timezone_names;
查詢 PRC 時(shí)區(qū)
select * from pg_timezone_names where name = 'PRC';
PRC是指中華人民共和國(guó) PRC（People’s Republic of China）。
修改時(shí)區(qū)
修改時(shí)區(qū)，設(shè)置成東八區(qū) 北京時(shí)間 UTC+8，默認(rèn)為session級(jí)配置
set time zone 'PRC'; select now(); -- 2023-07-24 17:21:05.086183 +08:00
修改時(shí)區(qū)，用戶級(jí)配置
alter role rolname set timezone='UTC'; -- 修改指定角色時(shí)區(qū)(rolname為角色名) alter role all set timezone='UTC'; -- 修改所有角色時(shí)區(qū)
修改時(shí)區(qū)，數(shù)據(jù)庫(kù)級(jí)配置
alter database dbname set timezone='UTC'; -- dbname為數(shù)據(jù)庫(kù)名稱
時(shí)間/日期操作符和函數(shù)
時(shí)間/日期操作符
| 操作符 | 例子 | 返回類(lèi)型 | 結(jié)果 |
|---|---|---|---|
| + | select date ‘2023-07-24’ + integer ‘7’; | date | 2023-07-31 |
| + | select date ‘2023-07-24’ + interval ‘1 hour’; | timestamp | 2023-07-24 01:00:00.000000 |
| + | select date ‘2023-07-24’ + time ‘15:16’; | timestamp | 2023-07-24 15:16:00.000000 |
| + | select interval ‘1 day’ + interval ‘1 hour’; | interval | 0 years 0 mons 1 days 1 hours 0 mins 0.0 secs |
| + | select timestamp ‘2023-07-24 15:16’ + interval ‘23 hours’; | timestamp | 2023-07-25 14:16:00.000000 |
| + | select time ‘01:00’ + interval ‘3 hours’; | time | 04:00:00 |
| - | select - interval ‘23 hours’; | interval | 0 years 0 mons 0 days -23 hours 0 mins 0.0 secs |
| - | select date ‘2023-07-24’ - date ‘2023-07-22’; | integer | 2 |
| - | select date ‘2023-07-24’ - integer ‘7’; | date | 2023-07-17 |
| - | select date ‘2023-07-24’ - interval ‘1 hour’; | timestamp | 2023-07-23 23:00:00.000000 |
| - | select time ‘05:00’ - time ‘03:00’; | interval | 0 years 0 mons 0 days 2 hours 0 mins 0.0 secs |
| - | select time ‘05:00’ - interval ‘2 hours’; | time | 03:00:00 |
| - | select timestamp ‘2023-07-24 23:00’ - interval ‘23 hours’; | timestamp | 2023-07-24 00:00:00.000000 |
| - | select interval ‘1 day’ - interval ‘1 hour’; | interval | 0 years 0 mons 1 days -1 hours 0 mins 0.0 secs |
| - | select timestamp ‘2023-07-24 03:00’ - timestamp ‘2023-07-24 12:00’; | interval | 0 years 0 mons 0 days -9 hours 0 mins 0.0 secs |
| * | select interval ‘1 hour’ * double precision ‘3.5’; | interval | 0 years 0 mons 0 days 3 hours 30 mins 0.0 secs |
| / | select interval ‘1 hour’ / double precision ‘1.5’; | interval | 0 years 0 mons 0 days 0 hours 40 mins 0.0 secs |
日期/時(shí)間函數(shù)：
| 函數(shù) | 描述 | 例子 | 返回類(lèi)型 | 結(jié)果 |
|---|---|---|---|---|
| age(timestamp, timestamp) | 第1個(gè)timestamp 減去 第2個(gè)timestamp | select age(‘2023-07-24’, ‘1997-10-26’); | interval | 25 years 8 mons 29 days 0 hours 0 mins 0.0 secs |
| age(timestamp) | 從current_date 減去 timestamp的值 | select age(timestamp ‘1997-10-26’); | interval | 25 years 8 mons 29 days 0 hours 0 mins 0.0 secs |
| current_date | 今天的日期 | select current_date; | date | 2023-07-24 |
| current_time | 現(xiàn)在的時(shí)間 | select current_time; | time | 07:53:43.911756 +00:00 |
| current_timestamp | 日期和時(shí)間 | select current_timestamp; | timestamp | 2023-07-24 07:54:19.495372 +00:00 |
| date_part(text, timestamp) | 獲取子域(等效于extract) | select date_part(‘hour’, timestamp ‘2023-07-24 15:56:34’); | double | 15 |
| date_part(text, interval) | 獲取子域(等效于extract) | select date_part(‘month’, interval ‘2 years 3 months’); | double | 3 |
| date_trunc(text, timestamp) | 截?cái)喑芍付ǖ木?/td> | select date_trunc(‘hour’, timestamp ‘2023-07-24 15:56:34’); | timestamp | 2023-07-24 15:00:00.000000 |
| extract(field from timestamp) | 獲取子域 | select extract(hour from timestamp ‘2023-07-24 15:56:34’); | double | 15 |
| extract(field from interval) | 獲取子域 | select extract(month from interval ‘2 years 3 months’); | double | 3 |
| localtime | 當(dāng)前時(shí)間 | select localtime; | time | 08:00:08 |
| localtimestamp | 當(dāng)前日期和時(shí)間 | select localtimestamp; | timestamp | 2023-07-24 08:05:03.650472 |
| now() | 當(dāng)前的日期和時(shí)間(等效于current_timestamp) | select now(); | timestamp | 2023-07-24 08:09:30.828408 +00:00 |
| timeofday() | 當(dāng)前日期和時(shí)間 | select timeofday(); | text | Mon Jul 24 08:09:51.870484 2023 UTC |
extract，date_part函數(shù)支持的field
extract，date_part 這兩個(gè)函數(shù)可以從日期時(shí)間值中提取指定的部分，例如年份、月份、小時(shí)等。extract 是一個(gè) PostgreSQL 特有的函數(shù)，而 date_part 在標(biāo)準(zhǔn) SQL 中也有定義，但兩者的功能類(lèi)似。
| 域 | 描述 | 例子 | 結(jié)果 |
|---|---|---|---|
| century | 世紀(jì) | select extract(century from timestamp ‘2023-07-24 15:56:34’); | 21 |
| day | (月份)里的日期域(1-31) | select extract(day from timestamp ‘2023-07-24 15:56:34’); | 24 |
| decade | 年份域除以10 | select extract(decade from timestamp ‘2023-07-24 15:56:34’); | 202 |
| dow | 每周的星期號(hào)(0-6；星期天是0) (僅用于timestamp) | select extract(dow from timestamp ‘2023-07-24 15:56:34’); | 1 |
| doy | 一年的第幾天(1 -365/366) (僅用于 timestamp) | select extract(doy from timestamp ‘2023-07-24 15:56:34’); | 205 |
| epoch | Unix時(shí)間戳 | select extract(epoch from timestamp ‘2023-07-24 15:56:34’); | 1690214194 |
| hour | 小時(shí)域(0-23) | select extract(hour from timestamp ‘2023-07-24 15:56:34’); | 15 |
| isodow | ISO 周幾（1-7，其中1代表星期一） | select extract(isodow from timestamp ‘2023-07-24 15:56:34’); | 1 |
| isoyear | ISO 年份 | select extract(isoyear from timestamp ‘2023-07-24 15:56:34’); | 2023 |
| millennium | 千年((年份/1000)+1) | select extract(millennium from timestamp ‘2023-07-24 15:56:34’); | 3 |
| microseconds | 微秒 | select extract(microseconds from TIME ‘15:56:34.5’); | 34500000 |
| millisecond | 毫秒 | select extract(millisecon from TIME ‘15:56:34.5’); | 34500 |
| minute | 分鐘(0-59) | select extract(minute from timestamp ‘2023-07-24 15:56:34’); | 56 |
| month | 月份，對(duì)于timestamp數(shù)值，它是一年里的月份數(shù)(1-12)；對(duì)于interval數(shù)值，它是月的數(shù)目，然后對(duì)12取模(0-11) | select extract(month from timestamp ‘2023-07-24 15:56:34’); | 7 |
| quarter | 季度，該天所在的該年的季度(1-4)(僅用于 timestamp) | select extract(quarter from timestamp ‘2023-07-24 15:56:34’); | 3 |
| second | 秒域，包括小數(shù)部分(0-59[1]) | select extract(second from timestamp ‘2023-07-24 15:56:34’); | 34 |
| week | 該天在所在的年份里是第幾周。 | select extract(week from timestamp ‘2023-07-24 15:56:34’); | 30 |
| year | 年份域 | select extract(year from timestamp ‘2023-07-24 15:56:34’); | 2023 |
數(shù)據(jù)類(lèi)型格式化函數(shù)
PostgreSQL格式化函數(shù)提供一套有效的工具用于把各種數(shù)據(jù)類(lèi)型(日期/時(shí)間、integer、floating point和numeric)轉(zhuǎn)換成格式化的字符串以及反過(guò)來(lái)從格式化的字符串轉(zhuǎn)換成指定的數(shù)據(jù)類(lèi)型。
to_char 函數(shù)第一個(gè)參數(shù)是待格式化的值，而第二個(gè)是定義輸出或輸出格式的模板。
| 函數(shù) | 描述 | 例子 | 返回類(lèi)型 | 結(jié)果 |
|---|---|---|---|---|
| to_char(timestamp, text) | 把時(shí)間戳轉(zhuǎn)換成字串 | select to_char(current_timestamp, ‘HH12:MI:SS’); | text | 06:03:19 |
| to_char(interval, text) | 把時(shí)間間隔轉(zhuǎn)為字串 | select to_char(interval ‘14h 6m 20s’, ‘HH24:MI:SS’); | text | 14:06:20 |
| to_date(text, text) | 把字串轉(zhuǎn)換成日期 | select to_date(‘25 Jul 2023’, ‘DD Mon YYYY’); | date | 2023-07-24 |
| to_timestamp(text, text) | 把字串轉(zhuǎn)換成時(shí)間戳 | select to_timestamp(‘25 Jul 2023’, ‘DD Mon YYYY’); | timestamp | 2023-07-24 00:00:00.000000 +00:00 |
| to_timestamp(double) | 把UNIX紀(jì)元轉(zhuǎn)換成時(shí)間戳 | select to_timestamp(1690179888); | timestamp | 2023-07-24 06:24:48.000000 +00:00 |
用于日期/時(shí)間格式化的模式：
| 模式 | 描述 |
|---|---|
| HH | 一天的小時(shí)數(shù)(01-12) |
| HH12 | 一天的小時(shí)數(shù)(01-12) |
| HH24 | 一天的小時(shí)數(shù)(00-23) |
| MI | 分鐘(00-59) |
| SS | 秒(00-59) |
| MS | 毫秒(000-999) |
| US | 微秒(000000-999999) |
| AM | 正午標(biāo)識(shí)(大寫(xiě)) |
| Y,YYY | 帶逗號(hào)的年(4和更多位) |
| YYYY | 年(4和更多位) |
| YYY | 年的后三位 |
| YY | 年的后兩位 |
| Y | 年的最后一位 |
| MONTH | 全長(zhǎng)大寫(xiě)月份名(空白填充為9字符) |
| Month | 全長(zhǎng)混合大小寫(xiě)月份名(空白填充為9字符) |
| month | 全長(zhǎng)小寫(xiě)月份名(空白填充為9字符) |
| MON | 大寫(xiě)縮寫(xiě)月份名(3字符) |
| Mon | 縮寫(xiě)混合大小寫(xiě)月份名(3字符) |
| mon | 小寫(xiě)縮寫(xiě)月份名(3字符) |
| MM | 月份號(hào)(01-12) |
| DAY | 全長(zhǎng)大寫(xiě)日期名(空白填充為9字符) |
| Day | 全長(zhǎng)混合大小寫(xiě)日期名(空白填充為9字符) |
| day | 全長(zhǎng)小寫(xiě)日期名(空白填充為9字符) |
| DY | 縮寫(xiě)大寫(xiě)日期名(3字符) |
| Dy | 縮寫(xiě)混合大小寫(xiě)日期名(3字符) |
| dy | 縮寫(xiě)小寫(xiě)日期名(3字符) |
| DDD | 一年里的日子(001-366) |
| DD | 一個(gè)月里的日子(01-31) |
| D | 一周里的日子(1-7；周日是1) |
| W | 一個(gè)月里的周數(shù)(1-5)(第一周從該月第一天開(kāi)始) |
| WW | 一年里的周數(shù)(1-53)(第一周從該年的第一天開(kāi)始) |
示例：
-- 查詢今天是今年的第幾天 select to_char(now(), 'DDD'); -- 205
擴(kuò)展
查詢某個(gè)日期是否在某段日期范圍，可以使用 > 和 < 判斷；如果使用了 between ，則前一個(gè)日期必須小于后一個(gè)日期。
示例：
-- between 錯(cuò)誤用法
select date '2023-07-24' between date('2023-07-25') - 1 and date('2023-07-25') - 7; -- false
-- between 正確用法
select date '2023-07-24' between date('2023-07-25') - 7 and date('2023-07-25') - 1; -- true總結(jié)
到此這篇關(guān)于PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)匯總的文章就介紹到這了,更多相關(guān)PostgreSQL設(shè)置時(shí)區(qū)時(shí)間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Postgresql的pl/pgql使用操作--將多條執(zhí)行語(yǔ)句作為一個(gè)事務(wù)
這篇文章主要介紹了Postgresql的pl/pgql使用操作--將多條執(zhí)行語(yǔ)句作為一個(gè)事務(wù)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql數(shù)據(jù)庫(kù)如何查看數(shù)據(jù)中表的信息
這篇文章主要給大家介紹了關(guān)于postgresql數(shù)據(jù)庫(kù)如何查看數(shù)據(jù)中表信息的相關(guān)資料,要查詢數(shù)據(jù)表信息,需要用到 系統(tǒng)表或系統(tǒng)視圖等,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-04-04
PostgreSQL配置遠(yuǎn)程連接簡(jiǎn)單圖文教程
這篇文章主要給大家介紹了關(guān)于PostgreSQL配置遠(yuǎn)程連接的相關(guān)資料,PostgreSQL是一個(gè)功能非常強(qiáng)大的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS),文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-12-12
PostgreSQL常用字符串函數(shù)與示例說(shuō)明小結(jié)
文章介紹了PostgreSQL中常用字符串函數(shù)的使用方法,包括空值處理、字符串位置查詢、長(zhǎng)度計(jì)算、大小寫(xiě)轉(zhuǎn)換、去除空格、連接、替換、匹配、拆分和截取等操作,感興趣的朋友跟隨小編一起看看吧2024-11-11
Postgresql根據(jù)響應(yīng)數(shù)據(jù)反向?qū)崿F(xiàn)建表語(yǔ)句與insert語(yǔ)句的過(guò)程
根據(jù)已有數(shù)據(jù),可構(gòu)建名為products的表,包含id（自增主鍵）、title（非空字符串）、progress（非空整數(shù)）三個(gè)字段,建表后,可通過(guò)insert語(yǔ)句插入數(shù)據(jù),這種反向操作有助于從現(xiàn)有數(shù)據(jù)結(jié)構(gòu)出發(fā),快速構(gòu)建數(shù)據(jù)庫(kù)表,并進(jìn)行數(shù)據(jù)填充,感興趣的朋友跟隨小編一起看看吧2022-02-02
postgresql如何查詢重復(fù)計(jì)數(shù)及去重查詢
這篇文章主要介紹了postgresql如何查詢重復(fù)計(jì)數(shù)及去重查詢問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-11-11
Postgresql在mybatis中報(bào)錯(cuò):操作符不存在:character varying == unknown的問(wèn)題
這篇文章主要介紹了Postgresql在mybatis中報(bào)錯(cuò)： 操作符不存在 character varying == unknown的問(wèn)題，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01
PostgreSQL排查連接鎖問(wèn)題的常用SQL語(yǔ)句
正常情況下,PostgreSQL只要連上了就能愉快地使用了,但是在一些特別的場(chǎng)景,如壓測(cè)或者某些不可描述的異常,會(huì)出現(xiàn)數(shù)據(jù)庫(kù)連接異常的情況,比如連接數(shù)占滿了,所以本文給大家介紹了PostgreSQL排查連接鎖問(wèn)題的常用SQL語(yǔ)句,需要的朋友可以參考下2024-04-04
PostgreSQL 的 COPY 命令是高效數(shù)據(jù)導(dǎo)入導(dǎo)出的核心工具,性能遠(yuǎn)超常規(guī) INSERT 語(yǔ)句,下面給大家介紹PostgreSQL 的 COPY 命令深度解析,感興趣的朋友跟隨小編一起看看吧2025-05-05
這篇文章主要介紹了postgresql 替換空格 換行和回車(chē)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01