php timestamp utc

Question

I have a PHP MySQL query that inserts some data into a MySQL database and it includes a timestamp.

Currently the INSERT query uses NOW() for the the timestamp column and it is saved in the database in the following format: 2012-07-24 13:13:02

Unfortunately for me the Server is not in my time zone and it is listed as America/Los_Angeles as shown print date_default_timezone_get();

I was hoping to do the following:

date_default_timezone_set('Europe/London');
$timefordbLondonEU = date('Y-m-d H:i:s', time());

and simply save into the database the $timefordbLondonEU in place of the NOW();

Is this a good way to save such data ?

Many Thanks,

Richard

[ADDED TEXT]

I changed the Type in the MySQL db to DateTime and did the following:

date_default_timezone_set('Europe/London');
$timefordbLondonEU = date('Y-m-d H:i:s', time());

It is working but Im still not getting the overall concept yet.

Assumptions based on your comments:

MySQL = Does not have a datatype UTC you simply use type INT.
Unix_TimeStamp() will save the current time or count? in UTC format such as 1343247227.
As UTC is a count from a common 0 point you can get any timezone from it. Assuming that you don't want a date before the reference 0 point in 1970.

My guess and lead on from what you have said is the best way to do it is save the time as UTC in an INT (1343247227) and then generate any time zones you want from there. Again assuming you don't need to store dates before the reference 0 point in 1970.

Equally why not store as datetime YYYY-MM-DD HH:MM:SS at a known timezone and then convert to UTC or other timezones. It all seems pretty messy =(

Store all your times as UTC, then convert them to what you desire on the way out. — Petah
– Petah, Commented Jul 25, 2012 at 22:41
Or, if you don't need to store dates before 1970, you can store them as Unix timestamps, by making the field an INT. — Waleed Khan
– Waleed Khan, Commented Jul 25, 2012 at 22:45

drew010 · Accepted Answer · 2012-07-25 22:45:55Z

7

As @Petah said in the comments, store your times in UTC and covert them in the application as needed.

Unix timestamps are in UTC so I usually store my times in the database as timestamps. This saves the headache and confusion of first converting to UTC to insert, and then from UTC when selecting.

That is, make your time field an INT type, and use the function UNIX_TIMESTAMP() in MySQL when you insert, or get the timestamp from PHP using the time() function.

When you fetch the timestamp from the DB it will be in UTC, but when you display it in your PHP application using date(), it will display in the server timezone, or whatever you set with date_default_timezone_set.

Therefore the following two queries will work:

INSERT INTO `table` (id, time) VALUES(NULL, UNIX_TIMESTAMP());

// or

$time = time();

$query = "INSERT INTO `table` (id, time) VALUES(NULL, $time);

If you want to select it from the DB as a DATETIME, you can do this:

SELECT *, FROM_UNIXTIME(time) as dt FROM `table` WHERE 1

The resulting dt column will be in the format yyyy-MM-dd hh:mm:ss.

You can format the numeric timestamp in PHP using date()

If the PHP version you have is 64-bit, you aren't limited to the 1970 - 2036 range, PHP will support 64-bit timestamps, just make sure to use a BIGINT column in MySQL in that case.

Hope that helps.

answered Jul 25, 2012 at 22:45

drew010

70.3k11 gold badges144 silver badges174 bronze badges

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

4 Comments

DevilCode Over a year ago

Ok this has probabely confused me more now .. but I changed the Type in the MySQL to DateTime and did the following: date_default_timezone_set('Europe/London'); $timefordbLondonEU = date('Y-m-d H:i:s', time()); Its working but Im still not getting this really. Assumption: MySQL = Does not have a datatype for UTC so you use type INT. Unix_TimeStamp() will save the current time or count? in UTC format such as 1343247227. As UTC is a count from a common 0 point you can get any timezone from it?

drew010 Over a year ago

A MySQL DATETIME column is just a column in YYYY-MM-DD HH:MM:SS format, timezone is irrelevant. It could be UTC, or any other timezone. Unix timestamps (stored as INT or BIGINT in MySQL) are integers and are always in UTC. They are the number of seconds that have passed since Midnight on January 1 1970 UTC. Because they are always UTC it makes it easy to convert to/from a given timezone. UNIX_TIMESTAMP() in MySQL saves the current timestamp in UTC. Did that clear up any confusion or make it worse?

DevilCode Over a year ago

Yeah it's getting there =D I guess the only question is to PicK the time-zone you want and go from there. Not a real fan of the UTC anymore seems like if you pick a time zone and use that as your reference in DateTime format you can do any Time zone converts or date changes you need without the 1970 limit should you need it. I'm guessing that you can always turn your time zone to UTC anyway if you need to. Is there any disadvantage to just using datetime ? Thanks again for your help btw!!

drew010 Over a year ago

The benefit to timestamps is that you fetch them from the database, and use them with PHP date functions, the timezone conversion to server time is automatic. Stored as a DateTime in non-UTC would require an extra step of taking it from the stored timezone to the user or server timezone, or converting to UTC to use w/ PHP date functions. W/ 64-bit PHP, there are no 1970 limits to worry about, you can set negative timestamps for dates prior to 1970 and the cutoff year is 292,277,026,596 so that shouldn't be an issue. Storing in UTC is pretty universal due to timestamps being UTC.

Collectives™ on Stack Overflow

php timestamp utc

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related