I have a SQL query where I am trying to pull trucks that have come to my shop. In this query I have: Truck_ID, Recieved_Date, Released_Date.

Here's a sample data set

I want my results to look like this, where the release date is empty, and the cars that have been released for more than 30 days, no longer show up.

In my where clause, I need to limit how far back the Released_Date(UnixTimeStamp) can be pulled. In this case, 30 days.

I tried this query:

SELECT
t.id,
t.recieved_date,
t.released_date
from trucks t
left join terminal te on te.id = t.terminal_id


where t.is_deleted = 0

and t.released_date between

UNIX_TIMESTAMP(convert_tz(date_format(curdate(), '%Y-%m-%d 00:00:0') - interval 30 day,te.time_zone,@@session.time_zone))

and

UNIX_TIMESTAMP(convert_tz(date_format(curdate(), '%Y-%m-%d 00:00:0') ,te.time_zone,@@session.time_zone))
;

When I run this query this is what I get:

the query got rid of the results past 30 days, but only pulls results where there is a released date. but I also want to see trucks that are still in the shop as well. How do I accomplish this?

CodePudding user response：

The records are not being selected because NULL does not fall between any two dates. You need to match both the records in your date range and those that are NULL. Do this with an OR statement, and use parenthesis to force the order of operations.

SELECT
t.id,
t.recieved_date,
t.released_date
from trucks t
left join terminal te on te.id = t.terminal_id


where t.is_deleted = 0

and 
((
t.released_date between

    UNIX_TIMESTAMP(convert_tz(date_format(curdate(), '%Y-%m-%d 00:00:0') - interval 30 day,te.time_zone,@@session.time_zone))

  and

    UNIX_TIMESTAMP(convert_tz(date_format(curdate(), '%Y-%m-%d 00:00:0') ,te.time_zone,@@session.time_zone))
)
OR t.released_date IS NULL )
;