Correctly converting to timezone from UTC in MySQL database with PHP

Question

I see that a stored date value in phpmyadmin is 2020-03-03. In PHP, I set the default timezone to UTC and the user's timezone to America/New_York:

date_default_timezone_set('UTC');
$userTimeZone = new DateTimeZone('America/New_York');

I retrieve my date from the database, set the timezone on the date, and make it into a string. It now equals a day earlier. (03-02-2020)

$dateNeeded = new DateTime($row['dateNeeded']); 
$dateNeeded->setTimeZone($userTimeZone);
$dateNeededStr = $dateNeeded->format('m-d-Y');

What did I do incorrectly here?

If you have no time component in your stored date, isn't it supposed to turn out an earlier date in a different timezone if that timezone is behind the one of the stored date? — El_Vanja
– El_Vanja, Commented Mar 3, 2020 at 16:30
I have no idea. Is that the problem here? The data type in MySQL is "date". — Chewie The Chorkie
– Chewie The Chorkie, Commented Mar 3, 2020 at 16:32

El_Vanja · Accepted Answer · 2020-03-03 16:36:54Z

1

It is not incorrect. Your date is stored without the time component, which means when you put it in a DateTime constructor, it will be created as midnight ($dateNeeded = new DateTime($row['dateNeeded']) will have the value of 2020-03-03 00:00:00.

That's in UTC timezone because you defined it so at the start. So when you change the timezone, it will move back (as New York is UTC - 5) and gain the value of 2020-03-02 19:00:00.

Since you're only outputting the date in your format (and not the time), it comes out as a day early.

answered Mar 3, 2020 at 16:36

El_Vanja

4,0435 gold badges22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chewie The Chorkie Over a year ago

How do I handle formatting it and ignore the time component? Maybe just don't assign a timezone with setTimeZone?

Illya · Accepted Answer · 2020-03-03 16:38:08Z

0

Dont use date_default_timezone_set('UTC'); because MySQL server will use server timezone not UTC,

In example,

Your server time zone is GMT +2

And you set your server timezone to GMT +0;

Then you read your date on database which stored on GMT + 2 time zone

When you try to convert it to other timezone, e.g. GMT + 5,

It will shift 7 hour not 5 hour

answered Mar 3, 2020 at 16:38

Illya

1,2751 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Correctly converting to timezone from UTC in MySQL database with PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related