Most accurate way to store date and time inside database

What is the most accurate way to store date and time inside database that will work on all common database engines?

I want to know:
1. The fields I should create, should I create one field for date and one field for time or one for both
2. The date format
3. The SQL statements that will be used to get data between two dates and also get the date and time information, then convert them to any other format
4. How can I show the date to the member according to THEIR time zone?
LVL 12
Mohamed AbowardaSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Atique AnsariCommented:
You can store current time stamp.

Change date and time to Unix timestamp and store it into the DB.
0
Ray PaseurCommented:
Specific answers.

1. Use one field, column definition is DATETIME
2. ISO-8601 format is the way to carry all internal representations of DATETIME values
3. Use the SQL reserved word BETWEEN x AND y
3. Convert ISO representations to other formats with PHP functions strtotime() and date(); you can also convert in SQL queries but I prefer to use PHP
4. Use PHP function date_default_timezone_set(); note that PHP and MySQL have different timezones and these must be coordinated

The article has all of this and more.  Hope you find it helpful.  Happy New Year! ~Ray
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

eriksmtkaCommented:
Well, my two cents.. I've always just stored date time as an INT in the database.. unless there is a good reason NOT to (like you'll be storing birthdays or know you'll be storing dates before 1970).  Dates like that come around a lot less frequently whereas using INTs to handle day-to-day transactional stuff is just easier to manage.
0
Ray PaseurCommented:
@eriksmtka: I don't think there is anything necessarily wrong with storing a Unix timestamp in a column defined as INT.  I just find it visually easier to understand the data when I keep it in the ISO-8601 format.  There are costs to data conversion but they are usually less than the costs of data-related errors.

Example: 1329269400.  What am I supposed to be doing then?  

Oh, wait! 2012-02-14T19:30:00 is 7:30pm on Valentines Day, so I should be taking my bride out to dinner. ;-)

All the best for 2012, ~Ray
0
Dave BaldwinFixer of ProblemsCommented:
You can get a date from a MySQL date-time column in any format you want including Unix Timestamp.  If you use the data-time types, then the date-time functions including arithmetic can be used.  If you use an int column, then you can only use numerical functions which know nothing about timezones and Daylight Savings Time.
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
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?
0
Dave BaldwinFixer of ProblemsCommented:
Probably the best way is to use javascript to get the current time on the viewers computer and use AJAX to tell your server what time they have.  Timezones are a pain.  IPs don't necessarily map them correctly.  Here is a discussion about it:

http://stackoverflow.com/questions/743505/how-to-get-time-zone-through-ip-address-in-php
0
Ray PaseurCommented:
how can I get the visitor timezone?

Please see Practical Application #8 in the article linked here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
1. Use one field, column definition is DATETIME

Open 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,
0
Ray PaseurCommented:
You cannot know the right (ie, client) time zone from the data base, unless you somehow store the client's timezone.  You may want to do that, but you need to be aware that in a mobile society the same people may visit your web site from many different time zones.

You also have to deal with the issue that clients may not know their time zones, or they may have the current time in their computers set incorrectly.

But that aside, here is an experiment you might want to set up.

1. Use date_default_timezone_set() to choose your server's timezone and follow the guidance in the article about how to set the MySQL time zone for the data base.
2. Use date('Z') to get the server's seconds from GMT
3. Put together a script that asks the client to choose her timezone (See http://us2.php.net/manual/en/timezones.php for the timezones) and that also uses the example in Practical Application #8 to get the client's seconds from GMT.
4. Send a link to this script to knowledgeable PHP programmers around the world.
5. Collect the submitted timezones responses along with the JavaScript UTC offsets from the PHP programmers.
6. Examine the responses for consistency and data integrity.  You can do this by taking the client-reported timezone and using date_default_timezone_set() to set your server timezone, then use date('Z') to get the seconds offset from UTC.  If this number of seconds is close to the number of seconds reported by JavaScript from the browser, you've probably got good data.

Unfortunately this experiment, while helpful in building an understanding of the way timezones work, will not help you with issues related to summer time (daylight savings time) unless you make it a regular practice to query the client-side machine for the local time every time a form is submitted.

Executive summary: This is only a little bit easier than dealing with real-time currency trading.
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
@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,
0
Ray PaseurCommented:
Upon receiving any arbitrary date (or date and time), you can use PHP function strtotime() to convert the date to a Unix timestamp, then you can use date('c') to convert the timestamp into an ISO-8601 standard representation of a date and time.  If you do this with only a date, without time, it will give you a time of midnight at the beginning of the day.

$external_thing = 'August 23, 2011';
$timestamp = strtotime($external_thing);
if ($timestamp)
{
    $iso_date = date('c', $timestamp);
}
else die("BOGUS: $external_thing");

$query = "INSERT INTO my_table ( my_date_time ) VALUES ( '$iso_date' );

See http://dev.mysql.com/doc/refman/5.6/en/datetime.html
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
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);

Open 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?
0
Ray PaseurCommented:
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.
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
@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!
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
I am closing this question and will open separate question for the question I asked in http:#37371938
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
Question closed. Thanks,
0
Ray PaseurCommented:
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
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
@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".
0
Ray PaseurCommented:
...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.
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
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;

Open in new window


Output:
1314114030
1314110430

Open in new window

0
Mohamed AbowardaSoftware EngineerAuthor Commented:
@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.
0
Ray PaseurCommented:
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
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
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;

Open 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?
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
@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:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27515236.html

Thank you,
0
Mohamed AbowardaSoftware EngineerAuthor Commented:
*Link correction*
The bonus question for Ray_Paseur is here: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27519957.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.