PostgreSQL的常用日期时间函数用法

我们在数据库查询的时候,经常会用到一些日期时间相关的函数,那么在Postgresql里面有哪些相关的常用函数呢?下面我给大家整理了一些函数与应用案例。

1.获取当前时间

now()clock_timestamp()current_timestamplocaltimestamp

select now(),clock_timestamp(), current_timestamp, localtimestamp;
              now             |        clock_timestamp        |              now             |         timestamp
------------------------------+-------------------------------+------------------------------+---------------------------
 2022-02-13 13:20:49.14742+08 | 2022-02-13 13:20:49.147556+08 | 2022-02-13 13:20:49.14742+08 | 2022-02-13 13:20:49.14742
(1 row)

如果我们想获取简短的时间,Postgresql为我们提供了下面的函数
current_datecurrent_timelocaltime

select current_date, current_time, localtime;
    date    |       timetz       |      time
------------+--------------------+-----------------
 2022-02-13 | 13:26:57.104903+08 | 13:26:57.104903
(1 row)

2.时间的加减

下面的写法,执行结果都一样

# select now() + interval '2 years';
# select now() + interval '2 year';
# select now() + interval '2 y';
# select now() + interval '2 Y';
# select now() + interval '2Y';
           ?column?
-------------------------------
 2024-02-13 13:32:21.421169+08
(1 row)

以此类推,月、日、小时、分钟、秒的加减计算也同理。

缩写含义
Y
M
W
D
H 小时
M 分钟
S

注意:

  1. 单写M会默认为分钟的加减,针对月的加减建议写完整的monthmonths或者简写mon
  2. interval部分在做+运算时可以不写。

3.格式化函数

3.1 时间转字符串

to_char(timestamp, text)

select to_char(now(), 'YYYY-MM-DD HH:MI:SS');
       to_char
---------------------
 2022-02-13 14:07:36
(1 row)

3.2 字符串转日期

to_timestamp(text, text)

# select to_timestamp('13 Feb 2022', 'DD Mon YYYY');
      to_timestamp
------------------------
 2022-02-13 00:00:00+08
(1 row)

3.4 Unix时间戳转时间

to_timestamp(unixtime)

select to_timestamp(1644734325);
      to_timestamp
------------------------
 2022-02-13 14:38:45+08
(1 row)

关于时间格式的模式,这里和常用的格式稍有区别,具体见下表

模式描述
HH 一天的小时数(01-12)
HH12 一天的小时数(01-12)
HH24 一天的小时数(00-23)
MI 分钟(00-59)
SS 秒(00-59)
MS 毫秒(000-999)
US 微秒(000000-999999)
AM 正午标识(大写)
Y,YYY 带逗号的年(4和更多位)
YYYY 年(4和更多位)
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
MONTH 全长大写月份名(空白填充为9字符)
Month 全长混合大小写月份名(空白填充为9字符)
month 全长小写月份名(空白填充为9字符)
MON 大写缩写月份名(3字符)
Mon 缩写混合大小写月份名(3字符)
mon 小写缩写月份名(3字符)
MM 月份号(01-12)
DAY 全长大写日期名(空白填充为9字符)
Day 全长混合大小写日期名(空白填充为9字符)
day 全长小写日期名(空白填充为9字符)
DY 缩写大写日期名(3字符)
Dy 缩写混合大小写日期名(3字符)
dy 缩写小写日期名(3字符)
DDD 一年里的日子(001-366)
DD 一个月里的日子(01-31)
D 一周里的日子(1-7;周日是1)
W 一个月里的周数(1-5)(第一周从该月第一天开始)
WW 一年里的周数(1-53)(第一周从该年的第一天开始)

4. 其它工具函数

4.1 时间间隔

age(timestamp, timestamp)

# select age('2022-02-13', '2022-01-01');
      age
---------------
 1 mon 12 days
(1 row)

当然也可以只输入一个参数,计算current_date与入参的时间间隔。

# select age(timestamp '2022-01-01'), age(now(), '2022-01-01');
      age      |              age
---------------+-------------------------------
 1 mon 14 days | 1 mon 14 days 15:52:27.657573
(1 row)

4.2 时间截取

date_part(text, timestamp)
date_part函数从日期或时间值中提取一个子字段。
该字段是确定什么的标识符领域从中提取资源。该字段的值必须在下面提到的允许值列表中:

century、decade、year、month、day、hour、minute、second、microseconds、milliseconds、
dow、doy、epoch、isodow、isoyear、timezone、timezone_hour、timezone_minute

函数返回类型为double precision的值。

extract(field from source)
extract函数是从日期或者时间数值里面抽取子域,比如年、月、日等。source必须是timestamptimeinterval类型的值表达式。field是一个标识符或字符串,是从源数据中的抽取的域。

# select date_part('hour', timestamp '2022-02-13 14:51:01'), extract(hour from timestamp '2022-02-13 14:51:01');
 date_part | date_part
-----------+-----------
        14 |        14
(1 row)

也可以截断至指定精度
date_trunc(text, timestamp)

# select date_trunc('hour', timestamp '2022-02-13 14:51:01');
     date_trunc
---------------------
 2022-02-13 14:00:00
(1 row)

执行结果,小时后的分和秒被置为0。

4.3 日期类型比较大小

Timestamp 关键字

select count(*) from table_name where start_time <= timestamp '2022-08-01 12:30:00' and end_time >= timestamp '2022-08-01 12:30:00'

如需转载,请注明出处: https://chadou.me/p/247

最新发布