Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am not sure how to specify my datatype as datetime or timestamp, I think I will need both of them but on the different events. My website sells products and services worldwide and also have an account system for user to login. Please clarify the following:

  • Current date and time customer buys products from my website: Datetime?
  • Delivery date and time based on their location and date/time purchased, calculated from our system: Datetime?
  • Last time they logged-in on their account: Timestamp?

    1. What is the code in php to store purchasing date (current date) and then store in the database?
    2. Please elaborate how to use each of them as after reading plenty of explanation on the internet, I still don't quite get it.
share|improve this question
    
A while back I had a large project where performance was an absolute must, after months of research we ended up going with a UTC timestamp stored in a varchar field, it proved to be faster and better for our needs (large social network) –  jasondavis Dec 21 '11 at 15:47
add comment

2 Answers

up vote 16 down vote accepted

MySQL timestamps:

  • Are stored in UTC

    They are converted to UTC on storage and converted back to your time zone on retrieval. If you change time zone settings, the retrieved values also change.

  • Can be automatically initialised and updated

    You can set their default value and / or auto update value to CURRENT_TIMESTAMP

  • Have a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC

Whereas MySQL datetime:

  • What you store is what you get ™.

  • Have a range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59

    Values outside the range may work - but only values within the range are guaranteed to work.

  • You can store dates where day or month is zero.

    This is the MySQL way of storing birthdays! You can't do that with a TIMESTAMP, with the only exception being the zero value of 0000-00-00.

  • You can store invalid dates, if you need them, in ALLOW_INVALID_DATES mode.

  • You can set default value to NOW() in some instances, but the preferable and more natural way of automatically initialised and updated dates is TIMESTAMP.

And of course there are also DATE and TIME, which work just like DATETIME, but of course DATE doesn't care for time and TIME doesn't care for date. All four data types work perfectly with the wide array of date and time functions, but when you are mixing data types you should be aware of conversion effects.

Now, to your question: You should use DATETIME everywhere. Not for technical reasons, but since you are still unclear on how MySQL works, DATETIME is the simpler choice. This will mean that you will have to calculate the current timestamp in PHP before storing, that's as easy as:

$mysqldate = date("Y-m-d H:i:s"); 

PHP's date function works like:

string date ( string $format [, int $timestamp = time() ] )

The "Y-m-d H:i:s" format is the one compatible with MySQL and by leaving the second parameter empty, date() calls time() to get the current UNIX timestamp.

Using a TIMESTAMP instead of a DATETIME has the added value of MySQL taking over the responsibility of deciding on the current timestamp, and you can skip the field when you are inserting / updating. But since you are already sending a query, and the code to get the current timestamp in PHP is minimal, you can safely go with DATETIME for everything.

As for the actual code to store the PHP timestamp into the database, you should look at PHP Data Objects, and if you are still unclear, ask on StackOverflow instead. But there are almost 1.5k related questions already, make sure you go through them before asking. Just a hint, prepared statements is how the cool kids do it.

share|improve this answer
    
Hmm, that could be a problem on how you send the query to MySQL, or in your table structure... StackOverflow is a better place for these kinds of problems, post a question there with your table structure and your full php code, and some details on what the problem is and what you've already tried. We don't really discuss implementation specifics here... –  Yannis Rizos Dec 21 '11 at 2:23
    
Thanks for your reply. I have tried using $mysqldate = date("Y-m-d H:i:s"); $query = INSERT INTO table VALUE ('".$mysqldate."') on my php code but it would be kept in MySQL as 0000-00-00 00:00:00 in Date (field) - Datetime (datatype). I intend to store the current Date and Time. If I set the datatype as Timestamp in the database, how to code in php? –  Modular Dec 21 '11 at 2:26
    
I'm not allowed to post any comment on Stackoverflow at the moment. Sounds a bit shame but that's how they did to me. –  Modular Dec 21 '11 at 2:28
    
I'm sorry for that, but if you are in temporary suspension mode there must be a good reason. Now one possible error in the code in your comment about is that you are naming the field as Date, that's a reserved word for the DATE datatype and strange things may happen (depending on MySQL version), so try renaming it to something like creationdate and see what happens. Also, from PHP you should treat TIMESTAMP fields the same as DATETIME fields, it doesn't matter really. Their format is the same. –  Yannis Rizos Dec 21 '11 at 2:34
    
Thanks a million. It's been sorted now after spending nearly the whole night trying to figure it out why. :) –  Modular Dec 21 '11 at 2:47
add comment

Well I look at this in very simple way. In your case i would use both datetime and timestamp. With using both you will not have any additional problems with storing data or something like that. One additional field (column) in your table is also not a big problem.

So in my point of view and experiences so far I usually use both. When you want to show a date to your visitor or make it understandable to visitors then display a date in datetime format.

Datetime format could be a pain when you want to calculate something (difference between 2 dates, get yesterday's date from current date, get 1 week ago from current date, get tomorrow date or something like that.) It's not really hard but you will usually need to convert them in timestamp and then do the stuff. And yes current datetime for example you get with: $current = date("Y-m-d"); or $current = date("Y-m-d H:i:s"); if you also want hours, minutes and seconds.

Timestamp is just a number 11 digit long which doesn't really "mean" anything. When you look at it you will not know which date it represent. So it's not really user friendly and you can't use it to just echo it and show it to your visitors for example. You will need to "transform" it into something readable. But it's also offers you an ability to easily convert it, and to calculate different dates. For example if you want to get timestamp for tomorrow at the same time you just add number of seconds to current date and you have tomorrow time stamp. Example: $tomorrow = time()+24*3600; You can also easily get a difference between two dates in seconds or anything like that.

So i would suggest to use both datetime and timestamp. Even if you use for example PhpMyadmin and want to quickly look at some data it will be easier for you when you see a date in date time format (example: 2011-12-20 10:15:20) as it will be if you just look at the 11digit number. So use both and then call and use the one which suits you better.

If you must or want to choose just one I would suggest timestamp otherwise use both.

share|improve this answer
    
Thank you. Very informative. I'll do that. –  Modular Dec 24 '11 at 7:59
    
-1 Timestamp is just a number 11 digit long which doesn't really "mean" anything MySQL timestamps are stored in the same format as datetime. You need to convert them to php timestamps as well, to do the calculations you describe, OR simply use MySQL's many date/time functions and do the calculations in the database. Your answer doesn't really make sense. –  Yannis Rizos Dec 29 '11 at 8:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.