postgres Date/Time 学习笔记

时间：2023-03-09 15:45:05
postgres Date/Time 学习笔记

一、Date/Time Types

参考文档：https://www.postgresql.org/docs/9.2/static/datatype-datetime.html

Types 别名 备注
timestamp [ (p) ] [ without time zone ] 日期+时间
timestamp [ (p) ] with time zone timestamptz 日期+时间
date 日期
time [ (p) ] [ without time zone ] 时间
time [ (p) ] with time zone timetz 时间
interval [ fields ] [ (p) ] 时间段

注：

1、sequelize 里常用的 DATE 类型指的是 postgres 的 timestamptz 类型

2、仅 Date 没有时区概念。

3、interval 这里不展开了，待写。

二、Date/Time Input/Output

前提：postgres 的时区设置成了 PRC（中国）。

1、普通值

例：2018-08-16 20:12:16+08

//存

INSERT INTO "public"."MemberOrderLasts"("id","mobile","last_time")

VALUES

(1,'13600000000','2018-08-16 20:12:16+08');

//取

select  "last_time"  from "MemberOrderLasts"  where id = '1'

//2018-08-16 20:12:16.920642+08

注：对于国内程序员来说，使用“/”容易产生歧义，最好使用“-”做分隔，即 2018-08-16 20:12:16。

2、特殊值

当在 SQL 命令中用作常量时，所有这些值都需要用单引号括起来。

Input String Valid Types Description
epoch date, timestamp 1970-01-01 00:00:00+00 (Unix system time zero)
infinity date, timestamp later than all other time stamps
-infinity date, timestamp earlier than all other time stamps
now date, time, timestamp current transaction's start time
today date, timestamp midnight today
tomorrow date, timestamp midnight tomorrow
yesterday date, timestamp midnight yesterday
allballs time 00:00:00.00 UTC 
INSERT INTO "public"."MemberOrderLasts"("id","mobile","last_time")

VALUES

(2,'13600000000','now');

三、Date/Time TimeZone

参考我另一篇：《从 moment -> nodejs -> sequelize -> postgres，你都得设置好时区》。

四、Date/Time Functions and Operators

参考文档：https://www.postgresql.org/docs/9.2/static/functions-datetime.html

1、运算符

+ / -（* / / 不赘述，具体看文档）

select date '2018-01-12' + interval '7'

-- 2018-01-12 00:00:07 

select "activatedAt" - "createdAt" AS "diff" from "Members" where id = '373'

-- 19 days 09:07:11.155

2、函数

Function Return Type Description Example Result
age( timestamp , timestamp ) interval Subtract arguments, producing a "symbolic" result that uses years and months age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
age( timestamp ) interval Subtract from current_date (at midnight) age(timestamp '1957-06-13') 43 years 8 mons 3 days
date_part( text , timestamp ) double precision Get subfield (equivalent to extract); see Section 9.9.1 date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part( text , interval ) double precision Get subfield (equivalent to extract); see Section 9.9.1 date_part('month', interval '2 years 3 months') 3
date_trunc( text , timestamp ) timestamp Truncate to specified precision; see also Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp ) double precision Get subfield; see Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval ) double precision Get subfield; see Section 9.9.1 extract(month from interval '2 years 3 months') 3
isfinite( date ) boolean Test for finite date (not +/-infinity) isfinite(date '2001-02-16') true
isfinite( timestamp ) boolean Test for finite time stamp (not +/-infinity) isfinite(timestamp '2001-02-16 21:28:30') true
isfinite( interval ) boolean Test for finite interval isfinite(interval '4 hours') true
justify_days( interval ) interval Adjust interval so 30-day time periods are represented as months justify_days(interval '35 days') 1 mon 5 days
justify_hours( interval ) interval Adjust interval so 24-hour time periods are represented as days justify_hours(interval '27 hours') 1 day 03:00:00
justify_interval( interval ) interval Adjust interval using justify_days and justify_hours, with additional sign adjustments justify_interval(interval '1 mon -1 hour') 29 days 23:00:00
常用：extract / date_part 和 date_trunc 函数
1、extract / date_part

select date_part('hour', timestamp '2001-02-16 20:38:40')

-- 20

select extract('hour' from timestamp '2001-02-16 20:38:40')

-- 20

2、date_trunc

select date_trunc('hour', timestamp '2001-02-16 20:38:40')

-- 2001-02-16 20:00:00

3、查询时间是否重叠

(start1, end1) OVERLAPS (start2, end2)

(start1, length1) OVERLAPS (start2, length2)

// 一、两种用法

// 1、`(start1, end1) OVERLAPS (start2, end2)`

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS

       (DATE '2001-10-30', DATE '2002-10-30');

Result: true

// 2、`(start1, length1) OVERLAPS (start2, length2)`

SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS

       (DATE '2001-10-30', DATE '2002-10-30');

Result: false

// 二、重叠判断 遵循左开右闭的原则

SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS

       (DATE '2001-10-30', DATE '2001-10-31');

Result: false

SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS

       (DATE '2001-10-30', DATE '2001-10-31');

Result: true

五、Current Date/Time

1、普通

注意：下面的日期/时间在同一个事务里都是不变的。（如果要变化，建议使用下面即将介绍的 statement_timestamp 或者 clock_timestamp ）。

（1）带时区信息

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME(precision)

CURRENT_TIMESTAMP(precision)

还记得上面说的特殊值 now 吗，实际上跟 CURRENT_TIMESTAMP 一样：

//下面三个完全相等

SELECT CURRENT_TIMESTAMP;

SELECT TIMESTAMP 'now';

SELECT now();
（2）不带时区信息

LOCALTIME

LOCALTIMESTAMP

LOCALTIME(precision)

LOCALTIMESTAMP(precision)

仅 Date 没有时区概念。

select CURRENT_TIMESTAMP

// 2018-09-13 15:10:05.639902+08

select LOCALTIMESTAMP

// 2018-09-13 15:10:05.639902

2、事务专用

为了保障同一事务中的多个修改具有相同的时间戳，所以 postgre 提供了针对性的时间函数：

（1） transaction_timestamp() 返回事务开始的时间

其实 transaction_timestamp = CURRENT_TIMESTAMP ，只是针对的场景不同，所以换了个名字。

（2） statement_timestamp() 返回当前语句的开始时间

statement_timestamp() 和 transaction_timestamp() 只在一个事务内的第一条命令返回值相同。

（3） clock_timestamp() 返回实际的当前时间，因此即使在单个 SQL 命令中它的值也会更改。

select clock_timestamp(),clock_timestamp()

-- 2020-03-05 16:59:04.264526+08 | 2020-03-05 16:59:04.264527+08 （不一样）

六、Delaying Execution

pg_sleep()让当前的会话进程休眠 seconds 秒以后再执行。

SELECT pg_sleep(1.5);

相关文章