CODEWITHSUNDEEP
postgresql
Souvik Ray
Souvik Ray

Reputation: 3018

How to select rows in a postgres table based on a date and store it in a new table?

I have a postgres table which has some data.Each row has a date associated with it.I want to extract rows for the dates which has the month as April.Here is a csv version of my postgres table data

,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

So I want only the rows which has a month of april data such that I will have a table which looks something like this

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

Now If I try to extract a particular date with the below query

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

I get the error message

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

How do I get the rows for the month of April and store it in a new table say april_data?

Below is the structure of my existing table

   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    |              |

Upvotes: 0

Views: 206

Answers (2)

Mabu Kloesen
Mabu Kloesen

Reputation: 1358

The type of column date in your table is timestamp with time zone which format will be YYYY:MM:DD HH24:MI:SS.MS. In the query you make an operation timestamp with time zone = date, so it will throw an error.

So, if you want to fix it, you should fix one side to the type of other.

In your case I suggest as below:

  1. Match exact 1 day.

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

  2. Match within 1 month.

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

    OR

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

    OR

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

  3. Match only April.

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

    OR

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

Hopefully this answer will help you.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246268

You need single quotes around the string literal.
PostgreSQL will automatically cast it to the correct data type (timestamp with time zone).

You could use the extract function to select only the dates from April:

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

Upvotes: 1

Related Questions