CODEWITHSUNDEEP
sqlpostgresqldatetime
Americo
Americo

Reputation: 919

Subtracting dates in the WHERE clause

I have the following query 

`SELECT t.client_id, 
 count(t.client_id) as trips_xdays
 FROM trips t
 JOIN users u ON t.client_id = u.usersid
 WHERE t.city_id = 12
 AND t.status = 'completed'
 AND ( date_trunc('day',t.dropoff_at) - date_trunc('day',u.creationtime) <= 30 days,    0:00:00)
 GROUP BY t.client_id`

and error when I try to constrain the query by <= 30 days, 0:00:00. However I thought that would be the correct format since I queried 

 `select date_trunc('day',t.dropoff_at) - date_trunc('day',u.creationtime)
 from trips t 
 inner join users u ON t.client_id = u.usersid`

by itself and it came back with responses in the format of 30 days, 0:00:00

Any suggestions on how to correctly query so I can constrain the query on <= 30 days?

Upvotes: 1

Views: 4693

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659137

Assuming we are dealing with data type timestamp, you can simplify:

SELECT t.client_id, count(t.client_id) AS trips_xdays
FROM   trips t
JOIN   users u ON t.client_id = u.usersid
WHERE  t.city_id = 12
AND    t.status = 'completed'
AND    t.dropoff_at::date < u.creationtime::date + 30
GROUP  BY 1;

A simple cast to date is shorter and you can just add integer to date.

Or for a slightly different result and faster execution:

...
AND    t.dropoff_at < u.creationtime + interval '30 days'

The last form can more easily use a plain index. And it measures 30 days exactly.

Upvotes: 3

Ron.B.I
Ron.B.I

Reputation: 2766

writing it from the top of my head , please verify correctness:

`SELECT t.client_id, 
 count(t.client_id) as trips_xdays
 FROM trips t
 JOIN users u ON t.client_id = u.usersid
 WHERE t.city_id = 12
 AND t.status = 'completed'
 AND ( DATEDIFF(day,t.dropoff_at,u.creationtime) <= 30)
 GROUP BY t.client_id`

I would also add that although I'm not familiar with the date_trunc method enough, but using <= 30 days seems to me as a big syntax error, should use 30, with combining output to look like 30 without the string suffix.

To make things more clear, comparing strings in order to actually compare numeric is IMHO bad practice.

source for datediff usage:

date diff usages

other related tips

Upvotes: 0

Americo
Americo

Reputation: 919

It looks like I was simply forgetting quotes. The query in its working order is:

SELECT t.client_id, 
count(t.client_id) as trips_xdays
FROM trips t
JOIN users u ON t.client_id = u.usersid
WHERE t.city_id = 12
AND t.status = 'completed'
AND ( date_trunc('day',t.dropoff_at) - date_trunc('day',u.creationtime) <= '30 days,      0:00:00')
GROUP BY t.client_id

Upvotes: 1

Related Questions