Question

postgresql根据已知的一个日期，查询数据库中这个日期所属的月份的所有数据

现在有一个表里有很多数据，而我有一个日期： Time = ‘2014-01-30’ ，如何根据这个时间去查询2014年一月的所有数据？

Answer 1

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

追问

内容丰富 ， 找不到重点