Welcome Guest, Not a member yet? Register   Sign In
MySQL Timezone/Date Question?

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:
$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

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:
$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!

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:


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

Theme © iAndrew 2016 - Forum software by © MyBB