Most accurate way to store date and time inside database

You can store current time stamp.

Change date and time to Unix timestamp and store it into the DB.

4. Use PHP function date_default_timezone_set(); note that PHP and MySQL have different timezones and these must be coordinated

When I use date_default_timezone_set(); it requires the time zone, how can I get the visitor timezone?

1. Use one field, column definition is DATETIME

Select allOpen in new window

I didn't find example in the article about getting the date and time from DATETIME field, can you show me SQL statement to add the current date/time or any other date/time to DATETIME field and how to read them from the database and get them in the right time zone.

Thanks,

@Ray_Paseur: Please refer to http:#37364521, my problem now is STORING the date in DATETIME field, I need the SQL statement that I will use to INSERT data into DATETIME database field, I also need PHP example on how to get the data from DATETIME zone inside date variable, like $time.

Thanks,

That's great, now one more thing is getting the date from the database DATETIME field, I tried using the following code but its not working:

$sql = mysql_query("SELECT * FROM users WHERE id = 1");
$data = mysql_fetch_assoc($sql);
$datetime = $data['datetime'];
$timestamp = strtotime($datetime);

Select allOpen in new window

How can I correct this code to get the DATETIME into timestamp PHP variable?

Additionally, does DATETIME field store information about the time zone as well as the date and time? also does all of this work perfectly on MS SQL Server, Oracle and other common databases?

Wow, what a lot of questions!  ;-)

This is a really good learning resource if you are new to PHP.
http://www.sitepoint.com/books/phpmysql4/

We have made pretty good progress with this EE question so I will sign off now.  Going forward, please do not use MySQL reserved words to name columns in a table,  If you do that your code will become confusing.  If you avoid that you will save yourself a lot of headaches.

@Ray_Paseur: Well, not really I am not new to PHP at all, I'm actually ranked in PHP in EE.. however I am new to dealing with date and time in PHP, thank you for practicing in this question!

I am closing this question and will open separate question for the question I asked in http:#37371938

Question closed. Thanks,

Please explain what is missing from the article and why you marked this down to a "B" -- I would like to upgrade the article, so if you can suggest an improvement I'll be glad to insert it.  Thanks, ~Ray

@Ray_Paseur: The article is great, however I marked this question to a "B" because I am still searching for how to get DATETIME from database including information about the time zone, I am able to get the date like 2012-01-01 12:00:00, however it's not storing any information about the time zone, though if I stored the date as INT in the database 'TIMESTAMP' it store information about the time zone.

So basically the 'B' is for the question not the article.

If you have any suggestion on how to get the data from DATETIME field correctly I would gladly request a moderator to change this question grade to "A".

...if I stored the date as INT in the database 'TIMESTAMP' it store information about the time zone.

That is not true.  If you store the Unix timestamp (which is an integer) you are storing the value of the PHP function time() which is the same the world around, as explained in the article.  It is the UTC (aka GMT) time value, and it tells you nothing about the client's time zone.  It depends on the server clock and time zone being set correctly.

MySQL has no inherent support for client timezones.  You can prove this to yourself if you define a DATETIME column and create the ISO-8601 date/time string in PHP.  You will see something like this:

2012-02-14T19:30:00-06:00

Next, insert that value into the DATETIME column and SELECT the DATETIME column.  You will find that the -06:00 part is missing.

In other words, what you want does not exist, and you must write the programming to acquire and store the information.  The article shows you how to acquire this information from the client browser in Practical Application #8.  The recommended validation experiment is explained in ID:37364793.  It's not rocket science - you just have to carry another column in your data base table (if you're willing to assume that the client will never change timezones).

The risks here include leap year and daylight savings time.  At present, I am -6 hours from GMT, but in the summer, I am -5 hours.  Another risk is that some parts of the world do not observe DST or start the observation on different dates.  And there are places in India that have timezones with 15-minute offsets.  China has one timezone for the whole country.  So just getting the hours offset from GMT may not be accurate enough, depending on your application needs.  Only you can determine that - it's not a technical question, it's a business operations question.

Okay, here is the problem, if I am storing data into DATETIME according to the server timezone, what if I moved the server somewhere else and changed the timezone? The dates in the database will be stored according to the old time zone so it would be invalid.

if I stored the date as INT in the database 'TIMESTAMP' it store information about the time zone.

I noticed that when I use strtotime() to get timestamp, the timestamp result get affected by the time zone, consider looking at this code:

date_default_timezone_set("America/Chicago");
$external_thing = 'August 23, 2011 10:40:30 AM';
$timestamp = strtotime($external_thing);
echo $timestamp;

echo "<br />";

date_default_timezone_set("America/New_York");
$external_thing = 'August 23, 2011 10:40:30 AM';
$timestamp = strtotime($external_thing);
echo $timestamp;

Select allOpen in new window

Output:

1314114030
1314110430

Select allOpen in new window

@Ray_Paseur: After finishing with this question, I will consider opening bonus question to give you more points, I appreciate your effort for solving this question.

if I stored the date as INT in the database 'TIMESTAMP' it store information about the time zone.

There may be some confusion of terms.  If you take the value of PHP time(), which is an integer, and store it in an INT, or DATETIME column, you are storing the UTC time value (the Unix timestamp).  This has no information about the time zone of either the server or the client.  However it is a consistent way to store the UTC time, and if your programming is based on UTC time, you would not need to be concerned if your server moved to a different time zone because there is no information about the time zone used in your application.  But that does not do anything to help with the issue of the client's time of day.

TIMESTAMP is a term of art in both PHP and MySQL.  It is a special column definition in MySQL.  This link has some good discussion.
http://stackoverflow.com/questions/409286/datetime-vs-timestamp

date_default_timezone_set("America/Chicago");
$external_thing = 'August 23, 2011 10:40:30 AM';
$timestamp = strtotime($external_thing);
echo $timestamp;

echo "<br />";

date_default_timezone_set("America/New_York");
$external_thing = 'August 23, 2011 10:40:30 AM';
$timestamp = strtotime($external_thing);
echo $timestamp;

Select allOpen in new window

Since time() returns Unix timestamp which is based on UTC timezone, why in the above code the first echo() prints 1314114030 and the second echo() prints 1314110430 while the date is the same in both timestamps?

Also, would you suggest that I set date_default_timezone_set(); to UTC or to the server time zone?

@Ray_Paseur: I figured out what I am looking for, I have requested moderator attention to change the question grade to 'A'.

I have also opened a bonus question to give you more points here:
https://www.experts-exchange.com/questions/27515236/Most-accurate-way-to-store-date-and-time-inside-database.html

Thank you,

*Link correction*
The bonus question for Ray_Paseur is here: https://www.experts-exchange.com/questions/27519957/Bonus-points-for-Ray-Paseur.html