Inserting current timestamp and retrieving according to timezone

Question

I have a "date" column of type timestamp in my table that is CURRENT_TIMESTAMP (on update). I retrieve it with PHP using: date('H:i a', strtotime($message->date) But the time displayed is inconsistent for different database/server hosts (each has its own timezone). Exampe of date format as shown in the database itself: 2018-05-06 20:20:22

What is the way to make the timestamp more general (perhaps unix time in seconds), and then in PHP retrieve it based on the user's timezone? Will I have to change the INSERT statement to explicitly insert a unix timestamp, or "CURRENT_TIMESTAMP" can do it too? (And then in PHP how to convert it)

Is your database field a timestamp type or a datetime type ? — Webvoid, Jun 08 '18 at 07:52
The usual approach is to use UTC internally everywhere (both in PHP and MySQL). But the important question is: do you know what the user's time zone is in the first place? — Álvaro González, Jun 08 '18 at 08:13

score 1 · Answer 1 · answered Jun 08 '18 at 07:55

1

You need to set the time zone which you need, let say you want the date in 'America/Los_Angeles' then you can get the time like this

$date = $message->date; //your date
$tz = new DateTimeZone('America/Los_Angeles'); // User's time zone

$date = new DateTime($date);
$date->setTimezone($tz);
echo $date->format('H:i a');

answered Jun 08 '18 at 07:55

DsRaj

2,288
1
16
26

Thank you! it does change, but, it's still +1 hour. I think because the "CURRENT TIMESTAMP" on the mysql column is based on the clock of the server, and not the global time? (because the current timestamp shows the time on my country, perhaps I need to change it as well?) I will have to change current_timestamp to be that of unix time I think? But how? – pileup Jun 08 '18 at 08:04
You need to store the time in UTC and then you can print in whatever timezone you need – DsRaj Jun 08 '18 at 08:30

Inserting current timestamp and retrieving according to timezone

1 Answers1