How to get datetime from default timezone (IST) to a different user based timezone

Question

I have this query in PHP Laravel:

$sensor_data = DB::table('devices_sensor_data as D')
                ->select(DB::raw('
                    D.id,
                    COALESCE(D.DeviceId,dx.DeviceId) AS DeviceId,
                    D.ENERGY_Total,
                    D.Time')
                )

                ->join(DB::raw('
                    (SELECT
                        MIN(CONVERT_TZ(Time, "'.$dbTz.'", "'.$usrTz.'")) min_time,
                        MAX(CONVERT_TZ(Time, "'.$dbTz.'", "'.$usrTz.'")) max_time,
                        DeviceId
                    FROM devices_sensor_data
                    WHERE DATE(Time) BETWEEN "'.$fromTzTime.'" AND "'.$toTzTime.'"
                    AND DeviceId IN (\''.$arrayDeviceID.'\')
                    GROUP BY DATE(Time), DeviceId ORDER BY DATE(Time)
                    ) AS dx'
                ),
                function($join)
                {
                    $join->on(DB::raw('D.Time = `dx`.`min_time` OR D.Time'), '=', 'dx.max_time');
                    $join->where('D.DeviceId', '=', DB::raw('dx.DeviceId'));
                })
                ->whereIn('D.DeviceId', array_keys($devicesArr))
                ->whereDate('D.Time', '>=', $fromTzTime)
                ->whereDate('D.Time', '<=', $toTzTime);

                $sensor_data = $sensor_data
                ->orderBy('D.DeviceId')
                ->orderBy('D.Time')
                ->get();

I want to select MIN and MAX based on different user based timezone than that is default, right now its Asia/Kolkata, so I want to select it based on eg. America/New_York.

It returns me the MIN and MAX as per IST timezone and just convert that into NewYork timing, but I want to fetch the MIN and MAX according to NewYork TimeZone.

Answer 1

If you want to use named time zones inside CONVERT_TZ function you must create, populate and maintain the time zone system tables. Once the tables are setup, you can use the function like so:

SELECT CONVERT_TZ('2021-10-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 2021-10-01 13:00:00
-- British territories use BST in summer, where it is 4:30 hours behind IST

SELECT CONVERT_TZ('2021-11-01 17:30:00', 'Asia/Kolkata', 'Europe/London');
-- 2021-11-01 12:00:00
-- Outside of summer, these territories use GMT which is 5:30 hours behind IST

If the time zone tables are empty, this function will return null. Very few time zone abbreviations are supported (e.g. UTC and GMT) so you must use city names.

For your particular example, just calculate the MIN/MAX then convert:

SELECT CONVERT_TZ(MIN(...), 'Asia/Kolkata', 'Europe/London')

Edit

Since the question is also tagged php, I would like to add that it is possible to perform the conversion in PHP using DateTime class. Time zone support is built into PHP. So if you have a date string in a known format and you know what time zone it is in, you can convert like so:

$date = DateTime::createFromFormat('Y-m-d H:i:s', '2021-10-01 17:30:00', new DateTimeZone('Asia/Kolkata'));
$date->setTimezone(new DateTimeZone('Europe/London'));
echo $date->format('Y-m-d H:i:s');
// 2021-10-01 13:00:00

$date = DateTime::createFromFormat('Y-m-d H:i:s', '2021-11-01 17:30:00', new DateTimeZone('Asia/Kolkata'));
$date->setTimezone(new DateTimeZone('Europe/London'));
echo $date->format('Y-m-d H:i:s');
// 2021-11-01 12:00:00

Answer 2

You indeed need to use CONVERT_TZ. Let me create a sample database along with data:

create table MyTimes(
  id int auto_increment primary key,
  moment timestamp
);

insert into MyTimes(moment) values
('2021-01-02 00:00:00'),
('2021-01-01 00:00:00'),
('2021-01-04 00:00:00'),
('2021-01-03 00:00:00');

and now let's select the minimum and maximum using timezone transition:

select min(convert_tz(moment, '+05:30', '+01:00')) minimum, max(convert_tz(moment, '+05:30', '+01:00')) maximum
from MyTimes;

Fiddle: http://sqlfiddle.com/#!9/cfca54f/4

Now, let's see what is happening in this example:

• we do a selection
• which returns a single record of aggregate data
• that contains the minimum and maximum
• of the timezone transition of moment
• using convert_tz
• passing moment as the first parameter
• its current timezone as the second parameter
• its target timezone as the third parameter

Answer 3

Longer-range solution...

Carefully pick between DATETIME and TIMESTAMP. Both can hold date+time.

Think of DATETIME as being a picture of a clock. When someone in a different timezone reads such a column, they see what you saw. No adjustment for timezone is made.

Be aware that DATETIME has hiccups twice a year if you timezone uses daylight savings.

Think of TIMESTAMP as being converted to UTC as it is stored and then converted back to the client's timezone when read. Or think of it as a point in time in the universe. This works better for announcing, say, a Zoom meeting time.

To work as intended (with TIMESTAMP), each client machine must be configured to the "local" timezone.

(There are few other cases of times that need adjusting; MySQL has only the above two.)