CodeIgniter Forums
MySQL Timezone/Date Question? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: MySQL Timezone/Date Question? (/thread-28652.html)



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