MySQL Timezone/Date Question? - Printable Version

MySQL Timezone/Date Question? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Timezone/Date Question? (/showthread.php?tid=28652)

MySQL Timezone/Date Question? - El Forum - 03-17-2010

[eluser]BobbyB[/eluser]
Hello,
I have a problem getting the newest articles from my database.
I try to get the number of news articles from the last 24 hours.
The date is stored in the database as "datetime".
I am using:

Code:
$query = $this->db->query('SELECT * FROM twitter_news WHERE tw_created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR)');

$data['count_new'] = $query->num_rows();

I figured that the timezone setting of the mysql server might be different, so I tried to change the interval accordingly.
But it doesn't work.
I tried changing it to

Code:
INTERVAL 3 HOUR

and the query is still giving me some items that are older than 24 hours?
How can I check what "CURRENT_DATE" is returning?
Any hints?
In a view of mine I use:

Code:
$now = time();

$unix1 = human_to_unix($tweet['tw_created_at']);

$timespan = $now-$unix1;

//

if($timespan < 86400){...

And it works great as expected - but not in my SQL query :-S
Thanks in advance!

MySQL Timezone/Date Question? - El Forum - 03-17-2010

[eluser]danmontgomery[/eluser]
CURRENT_DATE returns the current date with no time, so it's assumed 00:00:00 of today, so subtracting 24 hours from that would give you anything since 00:00:00 yesterday... I believe what you want is NOW() instead of CURRENT_DATE, which includes the time.

Also, to check what current_date is returning:

Code:
SELECT CURRENT_DATE;

MySQL Timezone/Date Question? - El Forum - 03-18-2010

[eluser]BobbyB[/eluser]
Hey noctrum,
thanks for your detailed reply.
CURRENT_DATE sounded kind of wrong to me, too.
But I didn't know I could use now() instead.
It works now Smile

Happy Happy - Joy Joy