Select * if today?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Select * if today?

Post by xterra »

Hello,
Can someone assist me in a SQL statement. Trying to select * from table where `UnixTime` is within 24 hours?


Thankyou,
Rob.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is `UnixTime` a unix timestamp? What do you consider within 24 hours (past, future, surrounding)?
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Thanks for the reply.

The unix time is the unix timestamp that is in the "myDate" column. So it will be something like:


select * from myTable where `myDate` within 24 hours. And by 24 hours, I mean from the start of the day. So if it's Friday night, I want all the records since friday morning at 12:00AM.


Hope this clarifies. Thanks in advance.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can use mktime() and some results from date() to create a timestamp for both the upper and lower limits. Using the BETWEEN clause in your SQL, you can then use that as the range. Alternately, FROM_UNIXTIME() and another function or two could be used if you want a purely SQL version.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Code: Select all

SELECT * FROM myTable WHERE FROM_UNIXTIME(myDate) > CURDATE();
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

feyd:
I took your advice this is as close as I can get, but it still returns nothing:

Code: Select all

$time = time();
$start_time = mktime(0, 0, 0, date('m', $time),date('d', $time),date('Y', $time)); 

$queryToday = "SELECT * FROM `Statistics` WHERE `ID` = '$ID'  BETWEEN $start_time AND $time";

//get all times from early this morning to now

Can you see a problem?

jamiel: no luck either, sorry.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

No field is specified for the BETWEEN to check.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

If you just want to select all from today, then format FROM_UNIXTIME() to = CURDATE();!


Like so...

Code: Select all

SELECT * FROM my_table WHERE FROM_UNIXTIME(myDate, '%Y-%m-%d') = CURDATE();

pif!
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Whoa. It worked. This doesn't make sense. But it works lol.

$queryToday = "SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = CURDATE()";



Thanks. It just looks weird.
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

If I want to find something from yesterday how come I can't just use curdate()-1?
57an
Forum Newbie
Posts: 4
Joined: Sat Jul 08, 2006 4:03 am

php running on its own

Post by 57an »

ok i see you are talking about something related to my question, but it is a little bit different.

Is it posible a php script to run on its own, not triggered by a user, but automatically. For example I want my php script to run every monday at 12:00. Is it posible and how is it posible?

According to me this is not possible, but one told me I'm wrong. So I'm getting confused. Thanx for the help in advance.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Re: php running on its own

Post by Oren »

57an wrote:ok i see you are talking about something related to my question, but it is a little bit different.

Is it posible a php script to run on its own, not triggered by a user, but automatically. For example I want my php script to run every monday at 12:00. Is it posible and how is it posible?

According to me this is not possible, but one told me I'm wrong. So I'm getting confused. Thanx for the help in advance.
I don't see how it is related, but just try to google for cron jobs.
xterra wrote:Whoa. It worked. This doesn't make sense. But it works lol.
It does make sense.
xterra wrote:If I want to find something from yesterday how come I can't just use curdate()-1?
For yesterday try:

Code: Select all

SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY)
xterra
Forum Commoner
Posts: 69
Joined: Mon Mar 06, 2006 12:52 pm

Post by xterra »

Thanks Oren. That was useful because I was able to put it in a for loop to get data for the last 7 days. Works well, if anyone wants to see:

Code: Select all

for ($i=0;$i<7;$i++)
{
 

$queryDay= "SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = (CURDATE() - INTERVAL $i DAY)";

$resultDay=mysql_query($queryDay);
$numDay=mysql_numrows($resultDay);

echo "$numDay";


}
Post Reply