DateTime->getTimeStamp() messes up timezone

Question

I am trying to import timestamps from an SQL table. The timestamps are in UTC, and I need to convert them to amsterdam local time. Currently I am using Datetime->format(), but I hear from all over the place that I should be using strftime() instead. I do indeed want to use strftime() since that seems the only method that respects language.

I'm having trouble converting DateTime objects to timestamps, and I'm also having trouble changing the formats' language.

setlocale(LC_TIME, "nl_NL");
$dbdate = "2019-06-24 18:02:30"; //SQL UTC timestamp
$date = DateTime::createFromFormat("Y-m-d H:i:s", $dbdate, new DateTimeZone('UTC'));
$date->setTimezone(new DateTimeZone('Europe/Amsterdam'));

$fdate0 = $date->format('l H:i Y-m-d');
$fdate1 = strftime("%A %R %F",$date->getTimestamp());

echo $fdate0." compared to ".$fdate1;

The actual output: Monday 20:02 2019-06-24 compared to Monday 11:02 2019-06-24

Expected output: Maandag 20:02 2019-06-24 compared to Maandag 20:02 2019-06-24

Note that the strftime method somehow misplaced 9 hours.

Edit: the exact same code now gives different results. strftime still loses 2 hours, but correct locale.

Previously I ran the code on http://sandbox.onlinephpfunctions.com/. Now I ran the code on my hosted server space.

    $sql = "SELECT `timestamp` FROM  `mytimes` 
WHERE  `id` =$cid;";
    $retval = mysqli_query($conn,$sql);
    if(!$retval){
        die("Could not get data: ".mysqli_error());
    }
    
    while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)){    
        setlocale(LC_TIME, "nl_NL");
        $dbdate = $row["timestamp"];
        $date = DateTime::createFromFormat("Y-m-d H:i:s", $dbdate, new 
        DateTimeZone('UTC'));
        $date->setTimezone(new DateTimeZone('Europe/Amsterdam'));
        
        //$fdate0 = $date->format('l H:i Y-m-d');
        $fdate1 = strftime("%A %R %F",$date->getTimestamp());
    }

This code now gives timestamps akin to Maandag 18:02 2019-06-24, which is in the correct language, but still UTC time for some reason. Why did it lose the Eu timezone???

Who told you to use `strftime`? I can't reproduce your issue: https://3v4l.org/dNQZ4 — Dharman, Jun 24 '19 at 18:44
See https://stackoverflow.com/questions/10909911/php-setlocale-has-no-effect - The locale may not be installed on the system itself, so changing the locale would not make a difference. That would also explain why the day does not change languages. — aynber, Jun 24 '19 at 18:47
Possible duplicate of [PHP setlocale has no effect](https://stackoverflow.com/questions/10909911/php-setlocale-has-no-effect) — aynber, Jun 24 '19 at 18:49
Also the manual says: *"This means that %e, %T, %R and, %D (and possibly others) - as well as dates prior to Jan 1, 1970 - will not work on Windows, some Linux distributions, and a few other operating systems."* — Dharman, Jun 24 '19 at 18:51
[Formatting DateTime object, respecting Locale::getDefault()](https://stackoverflow.com/a/16921843/1839439) — Dharman, Jun 24 '19 at 18:55
Try this (2nd answer, with intl extension, worked for me just now): https://stackoverflow.com/questions/8744952/formatting-datetime-object-respecting-localegetdefault — alx, Jun 24 '19 at 19:37
Also, if you still want to stick with `createFromFormat`, I think 3rd parameter is destination timezone, not source. I.e. if you specify `new DateTimeZone('Europe/Amsterdam')` as 3rd parameter and remove following `setTimetzone()` call, you'll get correct offset. — alx, Jun 24 '19 at 19:42
@alx nope, changing the 3rd argument didn't return the correctly offsetted time. I guess I'll have to check out that intl, yet another php date utility I have to learn... — AnnoyinC, Jun 24 '19 at 19:48
Changing 3rd argument helped me, weird that's not true for you. What is system TZ? What is your MySQL TZ? I mean, how do you know MySQL has UTC dates? Maybe it has some locale set, but dates are stored as if locale was UTC, and you see what I mean, there could be double conversion or something. To summarize, there are many places where TZ can be defined: global server TZ, mysql server global TZ, mysql connection-specific TZ, PHP TZ, TZ you set in PHP for your needs, and TZ of particular DateTime objects. Ah, and TZ of client browser (probably PHP won't pick it up without telling it to do so). — alx, Jun 24 '19 at 19:54
I don't know... Which is a pain. I don't even have access to php.ini. But I know it's UTC (or gmt) because "now()" gives the same time as googling "UTC time right now" — AnnoyinC, Jun 24 '19 at 19:56

score 0 · Answer 1 · answered Jun 24 '19 at 20:20

0

The timezone in question was not installed. In fact, my hoster does not have any timezones supported. Sigh.

Dirty hack that neglects DST: $timestamp += 2*60*60

answered Jun 24 '19 at 20:20

AnnoyinC

426
4
17

DateTime->getTimeStamp() messes up timezone

Edit: the exact same code now gives different results. strftime still loses 2 hours, but correct locale.

1 Answers1