postgresql日期和时间函数
postgresql的一些基本日期和时间函数
1,age(timestamp,timestamp) :两个时间具体时间差
postgres=# select age(timestamp '2019-01-09',timestamp '2011-03-25');
age
------------------------
7 years 9 mons 15 days
(1 row)
postgres=#
2,age(timestamp) :当前时间减去给定参数时间的时间差
postgres=# select age(timestamp '2001-01-23');
age
--------------------------
18 years 11 mons 16 days
(1 row)
3,clock_timestamp():实时时钟的当前时间戳
postgres=# select clock_timestamp();
clock_timestamp
-------------------------------
2020-01-08 23:59:08.504451-08
(1 row)
4,current_date:当前的日期
postgres=# select current_date;
current_date
--------------
2020-01-08
(1 row)
5,current_time:当前的时间
postgres=# select current_time;
current_time
--------------------
00:00:22.126313-08
(1 row)
6,current_timestamp:当前事务开始时的时间戳
postgres=# select current_timestamp;
current_timestamp
-------------------------------
2020-01-09 00:00:49.556473-08
(1 row)
7,date_part(text,timestamp):获取时间中指定的子域
postgres=# select date_part('hour',timestamp '2020-01-07 14:34:56');
date_part
-----------
14
(1 row)
postgres=# select date_part('month',timestamp '2020-01-07 14:34:56');
date_part
-----------
1
(1 row)
8,date_trunc(text,timestamp):通过指定的精度截断时间
postgres=# select date_trunc('hour',timestamp '2020-01-07 14:34:56');
date_trunc
---------------------
2020-01-07 14:00:00
(1 row)
9,extract(field from timestamp):类似date_part
postgres=# select extract(hour from timestamp '2020-01-07 14:34:56');
date_part
-----------
14
(1 row)
10,isfinite(timestamp):测试是否为有穷时间戳
postgres=# select isfinite(timestamp '2020-01-07 14:34:56');
isfinite
----------
t
(1 row)
11,isfinite(interval):测试是否为有穷时间间隔
postgres=# select isfinite(interval '5 hour');
isfinite
----------
t
(1 row)
12,justify_days(in-terval):按照每月30天调整时间间隔
postgres=# select justify_days(interval '30 days');
justify_days
--------------
1 mon
(1 row)
13,justify_hours(in-terval):按照每天24小时调整时间间隔
postgres=# select justify_hours(interval '24 hours');
justify_hours
---------------
1 day
(1 row)
14,justify_interval(in-terval):使用justify_days和justify_hours调整时间间隔的同时进行正负号调整
postgres=# select justify_interval(interval '1 mon - 1 hour');
justify_interval
------------------
29 days 23:00:00
(1 row)
15,localtime:当日时间
postgres=# select localtime;
localtime
-----------------
00:13:53.598568
(1 row)
16,localtimestamp:当前事务开始时的时间戳
postgres=# select localtimestamp;
localtimestamp
----------------------------
2020-01-09 00:14:15.896428
(1 row)
17,now():当前事务开始时的时间戳
postgres=# select now();
now
-------------------------------
2020-01-09 00:14:43.782863-08
(1 row)
18,statement_timestamp():实时时钟的当前时间戳
postgres=# select statement_timestamp();
statement_timestamp
------------------------------
2020-01-09 00:15:15.08981-08
(1 row)
19,timeofday():与clock_timestamp()作用相同
postgres=# select timeofday()
postgres-# ;
timeofday
-------------------------------------
Thu Jan 09 00:15:42.223596 2020 PST
(1 row)
20,transaction_timestamp():当前事务开始时的时间戳
postgres=# select transaction_timestamp();
transaction_timestamp
-------------------------------
2020-01-09 00:16:16.217301-08
(1 row)
欢迎大家关注以下公众号进行数据库方面知识探讨:
上一篇: 写个一个简单的canvas图表