postgreSQL：按日期查询

发布网友发布时间：2022-04-22 10:32

共1个回答

热心网友时间：2023-09-27 20:36

david=# select extract(year from now());
date_part
-----------
2013
(1 row)
david=# 3.2 取月份

david=# select extract(month from now());
date_part
-----------
4
(1 row)
david=#
david=# select extract(day from timestamp '2013-04-13');
date_part
-----------
13
(1 row)
david=#
david=# SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
date_part
-----------
40
(1 row)
david=# 3.3 查看今天是一年中的第几天

david=# select extract(doy from now());
date_part
-----------
102
(1 row)
david=# 3.4
查看现在距1970-01-01 00:00:00 UTC 的秒数

david=# select extract(epoch from now());
date_part
------------------
1365755907.94474
(1 row)
david=# 3.5 把epoch 值转换回时间戳

david=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1369755555 * INTERVAL '1 second';
?column?
------------------------
2013-05-28 23:39:15+08
(1 row)
david=#

--取day
skytf=> select extract(day from now());
date_part
-----------
3
(1 row)
skytf=> select extract(day from timestamp '2011-06-03');
date_part
-----------
3
(1 row)
skytf=> select timestamp '2011-06-03';
timestamp
---------------------
2011-06-03 00:00:00
(1 row)

--取小时
skytf=> select extract (hour from now());
date_part
-----------
14
(1 row)

--取分钟
skytf=> select extract (minute from now());
date_part追问内容丰富，找不到重点