CODEWITHSUNDEEP
mysql
Stephen
Stephen

Reputation: 19

MySQL Query looking for 24 hrs of data only

I am trying to retrieve only the data for a 24 hr period.

The sql I have is:

SELECT * FROM aprstrack WHERE callsignSSID = 'VE9SC-9' AND
reporttime BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE()) ORDER BY
reporttime ASC

can someone tell me where I am going wrong. Thanks in-advance.

Upvotes: 1

Views: 1003

Answers (3)

John Woo
John Woo

Reputation: 263733

remove extra brcket from SYSDATE())

SELECT * 
FROM aprstrack 
WHERE callsignSSID = 'VE9SC-9' 
        AND
reporttime BETWEEN (SYSDATE() - INTERVAL 1 DAY) AND SYSDATE() 
ORDER BY reporttime ASC

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95592

In standard SQL, you need to quote the number in that context.

reporttime BETWEEN SYSDATE() - INTERVAL '1' DAY AND ...

MySQL documentation suggests that you don't need to do that in MySQL. I'd be suspicious, and test. (I don't have a MySQL server instance running here, or I'd test it for you.)

You probably want NOW() instead of SYSDATE(). SYSDATE() isn't deterministic within a single SQL statement, so it's possible (but unlikely) for the two calls to SYSDATE() to return different values.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65284

You have an unbalanced bracket, 

SELECT * FROM aprstrack WHERE callsignSSID = 'VE9SC-9' AND
reporttime BETWEEN SYSDATE() - INTERVAL 1 DAY AND SYSDATE() ORDER BY
reporttime ASC

Upvotes: 2

Related Questions