PostgreSQL提供了大量用于獲取系統(tǒng)當(dāng)前日期和時(shí)間的函數(shù),例如 current_date、current_time、current_timestamp、clock_timestamp()等,這篇文章主要給大家介紹了關(guān)于PostgreSql日期類型處理的相關(guān)資料,需要的朋友可以參考下

1. 查詢天數(shù)據(jù)

查詢當(dāng)天數(shù)據(jù)

select * from table1 as n
where n.created_time>=current_date;

查詢昨天數(shù)據(jù)

select * from table1 as n
where n.created_time>=current_date-1 and n.created_time <current_date ;

2. 查詢?cè)聰?shù)據(jù)

查詢當(dāng)月數(shù)據(jù)

select *
from table1 as n
WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now())
and extract(MONTH FROM created_time) = extract(MONTH FROM now())

查詢上月數(shù)據(jù)

select *
from table1 as n
where created_time >= date_trunc('month',current_date - interval '1' month)
and created_time < date_trunc('month',current_date)

3. 查詢年數(shù)據(jù)

查詢當(dāng)年數(shù)據(jù)

select *
from table1 as n
WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now()) ORDER BY created_time

查詢?nèi)ツ陻?shù)據(jù)

select *
from table1 as n
where created_time >= date_trunc('year',current_date - interval '1' year)
and created_time < date_trunc('year',current_date)

4.類型轉(zhuǎn)換

1.查詢某天：datetime類型的，需要轉(zhuǎn)換為 date 類型，如果你要查詢的字段已經(jīng)是 date 類型則不需要進(jìn)行轉(zhuǎn)換

select t_create
from table
where t_create::date = to_date(‘2023-02-08', ‘YYYY-MM-DD');

2.string轉(zhuǎn)timestamp類型,按范圍查詢

select * from table where create_date >= ‘2023-01-08'::timestamp and create_date < ‘2023-02-08'::timestamp;

3.時(shí)間戳Long轉(zhuǎn)Timestamp

select TO_TIMESTAMP(1512490630)

4.string轉(zhuǎn)data,只能得到年月日，得不到時(shí)分秒

select to_date(‘2023-01-28 12:55:05')

5.當(dāng)前日期 select current_date

6.帶時(shí)區(qū)的時(shí)分秒值 select current_time;也可以使用current_time(precision)，將結(jié)果在四分之一秒的范圍內(nèi)四舍五入到位數(shù)，比如select current_time(2)；對(duì)應(yīng)沒(méi)有時(shí)區(qū)的值：select localtime;

7.帶時(shí)區(qū)的年月日時(shí)分秒值 select current_timestamp; 對(duì)應(yīng)沒(méi)有時(shí)區(qū)的值：select localtimestamp;

補(bǔ)充：時(shí)區(qū)轉(zhuǎn)換

有些時(shí)候，時(shí)區(qū)轉(zhuǎn)換對(duì)于特定時(shí)間在不同時(shí)區(qū)顯示特別有用。AT TIME ZONE提供了這種功能，它是如何做到的？我們將在一個(gè)事務(wù)中進(jìn)行演示，因?yàn)橥皇聞?wù)中now()函數(shù)總是返回相同的值，從而我們可以很容易看到同一時(shí)間在不同時(shí)區(qū)顯示的差別。

postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
       now
-------------------------------
 2013-08-26 12:39:39.122218+02
postgres=# SELECT now() AT TIME ZONE 'GMT';
     timezone
----------------------------
 2013-08-26 10:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'GMT+1';
     timezone
----------------------------
 2013-08-26 09:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'PST';
     timezone
----------------------------
 2013-08-26 02:39:39.122218