-1

I am trying to set a query that will return the user ids of registrations that happened in the last minute (between now and the last minute).

So far I am trying this:

SELECT member_id 
FROM members 
WHERE registration_date 
BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 MINUTE ) ) AND DATE ( NOW() )

But I don't think it is working. It is not returning any records. Can someone point me in the right direction?

The registration column is on the form:

Y-m-d H:i:s

Please any help will be greatly appreciated.

Thank you!

Barmar
  • 741,623
  • 53
  • 500
  • 612
karlosuccess
  • 843
  • 1
  • 9
  • 25
  • So your registration column is a VARCHAR? Then you can not use a DATE to compare it with. You have to cast your registration column to a DATE first. Best is to save the data as a date since casting your data to date is slow and prevents use of any indexes. – Norbert Jul 10 '15 at 21:02
  • 1
    no need for a between. `.. where registration >= now() - interval 1 minute`. and is the registration column a date type, or a varchar/text? – Marc B Jul 10 '15 at 21:02
  • @MarcB unless he allows registration in the future :) – Barmar Jul 10 '15 at 21:03
  • The registration column is of the type "datetime" – karlosuccess Jul 10 '15 at 21:20
  • If someone registers right now right at this second and I go to the database and see, the database stores a registration time about 10 hours ahead of my time. Then I thought in using `date_default_timezone_set` to match my script to the database's time. But I am not sure if that is what I am supposed to do.... – karlosuccess Jul 10 '15 at 21:33

1 Answers1

0

Don't call DATE(), since that's removing the time of day from the values. 

SELECT member_id 
FROM members 
WHERE registration_date 
BETWEEN DATE_SUB(NOW(), INTERVAL 1 MINUTE) AND NOW()
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • If someone registers now and I check it now right away, it shows in the database a registration datetime about 10 hours ahead of my time. Thus I thought in using `date_default_timezone_set` to match my script's time to the database. But I dont know If I am doing right or even if that is what I am supposed to do... – karlosuccess Jul 10 '15 at 21:31
  • The PHP timezone has no effect on MySQL. Use the MySQL `set timezone = 'xxxx'` query to set the timezone that your MySQL session uses. – Barmar Jul 10 '15 at 21:34
  • If I run the query set `timezone = 'Asia/Colombo'`, is that a permanent change? What about the records that where there before making this change? Do they get updated? – karlosuccess Jul 10 '15 at 21:41
  • No, it's only for the current MySQL session. – Barmar Jul 10 '15 at 21:42
  • Times are stored internally in the DB in universal time. When they're input and output, they get converted according to the session timezone. So it won't affect any data in the database. – Barmar Jul 10 '15 at 21:44
  • As I am saying, if someone registers right now, and I check it now, the database stores a datetime 10 hours ahead of me, because I think the server is in India, so if I run my script (also right now) to fetch registrations in the last hour, it will not return anything because the date is in the "future", right? – karlosuccess Jul 10 '15 at 21:45
  • No, the time isn't in the future. It's the current time, it's just being displayed in a different timezone than your PC's clock. – Barmar Jul 10 '15 at 21:48
  • Honestly I am confused because if I do a dummy registration at this second, right now my time is 3:30pm, `echo date("H:i")` outputs 7:30pm and the database says registration was made 11:30pm... so I am confused. sorry I am not the best at working with mysql times... – karlosuccess Jul 10 '15 at 21:50
  • I think I understand! I will give a shot to `set timezone = 'XYZ'`. but beforehand should I also set `date_default_timezone_set('XYZ')`? Both the same timezone? or not necessary? – karlosuccess Jul 10 '15 at 21:53
  • Running `set timezone = 'Asia/Colombo'` gave an error... _Unknown system variable 'timezone'_ ... any thoughts please? – karlosuccess Jul 10 '15 at 23:00
  • See http://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465#14454465 for how to update MySQL's timezone table. – Barmar Jul 10 '15 at 23:08