How can i get today recent record in database by passing user timezone date?

Question

I tried but didn't get the success in this scenario.

actually data values are saving based on users timezone dates,

so i want dynamically passing some parameters to check and get the values from database. but problem is how can i pass parameter as user time to mysql query?

Per day if 10 records are there for User1, i want recent past one hour record based on user timezone date only.

$select = db_select('{emp}', 'e');
$select -> addField('e', 'name', 'name');
$select -> addField('e', 'date', 'date');
$select -> condition('e.name', $name);
$select->where('date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)');
$select -> orderBy('e.date', 'DESC')->range(0,1);
$subresult = $select -> execute()-> fetchObject();

in that query now() taking as a system time, in that place how can i pass user's time?

any help?

Answer 1

See this. How to handle MySQL timezone in script

To store time data so it looks right to users in different time zones, you need to do several things.

First, ask each user to specify her timezone. These are specified as strings like 'America/New_York' and 'Asia/Dili'. Store that information as part of the each user's profile.

Next, each time you make a connection from your application to MySQL from your application, set the connection time zone with a command like this:

SET time_zone = users-time-zone-choice

Finally, always store time information in TIMESTAMP data types, rather than DATE or DATETIME datatypes. TIMESTAMP values are always converted from local time to UTC upon storage, and always converted back to local time upon retrieval.

So timestamp_column >= NOW() - INTERVAL 2 HOUR and similar queries work correctly.

datetime_column >= NOW() - INTERVAL 2 HOUR won't work.

Then, the NOW() function will display in the user's local time. So will the contents of TIMESTAMP data items. The contents of DATETIME and DATE objects will not display in users' local time; they are always stored and retrieved without respect to timezone.

It's best to set your server default timezone to UTC.

You can try this out; these commands should give you some interesting results.

set time_zone = 'Asia/Dili';
select @@time_zone, now(), utc_timestamp();
set time_zone = 'America/Phoenix';
select @@time_zone, now(), utc_timestamp();
set time_zone = 'Asia/Jerusalem';
select @@time_zone, now(), utc_timestamp();