如何根据日期选择postgres表中的行并将其存储在新表中?

Question

我有一个postgres表，里面有一些数据。每行都有一个与之关联的日期。我想提取月份为 4 月的日期的行。这是我的 postgres 表数据的 csv 版本

,date,location,device,provider,cpu,mem,load,drops,id,latency,gw_latency,upload,download,sap_drops,sap_latency,alert_id
0,2018-02-10 11:52:59.342269+00:00,BEM,10.11.100.1,COD,6.0,23.0,11.75,0.0,,,,,,,,
1,2018-02-10 11:53:04.006971+00:00,VER,10.11.100.1,KOD,6.0,23.0,4.58,0.0,,,,,,,,
2,2018-03-25 20:28:36.186015+00:00,RET,10.11.100.1,POL,7.0,26.0,9.83,0.0,,86.328,5.0,4.33,15.33,0.0,23.0,
3,2018-03-25 20:28:59.155453+00:00,ASR,10.12.100.1,VOL,5.0,14.0,2.67,0.0,,52.406,12.0,2.17,3.17,0.0,28.0,
4,2018-04-01 13:16:44.472119+00:00,RED,10.19.0.1,SEW,6.0,14.0,2.77,0.0,,52.766,2.0,3.25,2.29,0.0,1.0,0.0
5,2018-04-01 13:16:48.478708+00:00,RED,10.19.0.1,POL,6.0,14.0,4.065,0.0,,52.766,1.0,6.63,1.5,0.0,1.0,0.0
6,2018-04-06 21:00:44.769702+00:00,GOK,10.61.100.1,FDE,4.0,22.0,3.08,0.0,,54.406,8.0,3.33,2.83,0.0,19.0,0.0
7,2018-04-06 21:01:07.211395+00:00,WER,10.4.100.1,FDE,3.0,3.0,9.28,0.0,,0.346,2.0,10.54,8.02,0.0,33.0,0.0
8,2018-04-13 11:18:08.411550+00:00,DER,10.19.0.1,CVE,14.0,14.0,7.88,0.0,,50.545,2.0,6.17,9.59,0.0,1.0,0.0
9,2018-04-13 11:18:12.420974+00:00,RTR,10.19.0.1,BOL,14.0,14.0,1.345,0.0,,50.545,1.0,2.26,0.43,0.0,1.0,0.0

所以我只想要具有四月数据的行，这样我就会有一个看起来像这样的表格

4,2018-04-01 13:16:44.472119+00:00,RED,10.19.0.1,SEW,6.0,14.0,2.77,0.0,,52.766,2.0,3.25,2.29,0.0,1.0,0.0
5,2018-04-01 13:16:48.478708+00:00,RED,10.19.0.1,POL,6.0,14.0,4.065,0.0,,52.766,1.0,6.63,1.5,0.0,1.0,0.0
6,2018-04-06 21:00:44.769702+00:00,GOK,10.61.100.1,FDE,4.0,22.0,3.08,0.0,,54.406,8.0,3.33,2.83,0.0,19.0,0.0
7,2018-04-06 21:01:07.211395+00:00,WER,10.4.100.1,FDE,3.0,3.0,9.28,0.0,,0.346,2.0,10.54,8.02,0.0,33.0,0.0
8,2018-04-13 11:18:08.411550+00:00,DER,10.19.0.1,CVE,14.0,14.0,7.88,0.0,,50.545,2.0,6.17,9.59,0.0,1.0,0.0
9,2018-04-13 11:18:12.420974+00:00,RTR,10.19.0.1,BOL,14.0,14.0,1.345,0.0,,50.545,1.0,2.26,0.43,0.0,1.0,0.0

现在，如果我尝试使用以下查询提取特定日期

select * from metrics_data where date = 2018-04-13;

我收到错误消息

No operator matches the given name and argument type(s). You might need to add explicit type casts.

如何获取 4 月份的行并将其存储在新表中，例如april_data？

下面是我现有表的结构

Column    |           Type           | Modifiers | Storage  | Stats target | Description 
-------------+--------------------------+-----------+----------+--------------+-------------
date        | timestamp with time zone |           | plain    |              | 
location    | character varying(255)   |           | extended |              | 
device      | character varying(255)   |           | extended |              | 
provider    | character varying(255)   |           | extended |              | 
cpu         | double precision         |           | plain    |              | 
mem         | double precision         |           | plain    |              | 
load        | double precision         |           | plain    |              | 
drops       | double precision         |           | plain    |              | 
id          | integer                  |           | plain    |              | 
latency     | double precision         |           | plain    |              | 
gw_latency  | double precision         |           | plain    |              | 
upload      | double precision         |           | plain    |              | 
download    | double precision         |           | plain    |              | 
sap_drops   | double precision         |           | plain    |              | 
sap_latency | double precision         |           | plain    |              | 
alert_id    | double precision         |           | plain    |              | 

Answer 1

表中列date的类型timestamp with time zoneYYYY:MM:DD HH24:MI:SS.MS格式。在查询中，您将操作timestamp with time zone = date，因此它将引发错误。

因此，如果要修复它，则应将一侧固定为另一侧的类型。

在您的情况下，我建议如下：

1. 匹配正好 1 天。

   select * from metrics_data where date(date) = '2018-04-13';

2. 1个月内匹配。

   select * from metrics_data where date BETWEEN '2018-04-01 00:00:00' AND '2018-04-30 23:59:59.999';

   或

   select * from metrics_data where date(date) BETWEEN '2018-04-01' AND '2018-04-30';

   或

   select * from metrics_data where to_char(date,'YYYY-MM') = '2018-04';

3. 仅匹配April.

   select * from metrics_data where to_char(date,'MM') = '04';

   或

   select * from metrics_data where extract(month from date) = 4;

希望这个答案对您有所帮助。

Answer 2

字符串文字需要单引号。
PostgreSQL会自动将其转换为正确的数据类型(timestamp with time zone)。

您可以使用extract函数仅选择四月的日期：

SELECT * FROM yourtable WHERE extract (month FROM yourtable.date) = 4;