Mysql: Difference between datetime and timestamp in mysql

It's important to note that datetime has a range of 1000-9999, but the range for timestamp is only 1970-2038. this can be a problem if your system has to store birthdates, or you have to handle something like the payment plan for a 30-year mortgage.

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition: CREATE TABLE foo ( creation_time DATETIME DEFAULT CURRENT_TIMESTAMP, modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP )       In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in. You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdate. 1. TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.2. Timestamps are also lighter on the database and indexed faster. 3. DATETIME is constant while TIMESTAMP is effected by the time_zone setting. A timestamp field is a special case of the datetime field. You can create timestamp columns to have special properties; it can be set to update itself on either create and/or update. In "bigger" database terms, tiemstamp has a couple of special-case triggers on it. Not sure if this has been mentioned already, but worth noting in MySQL you can use something along the lines of below when creating your table columns on update CURRENT_TIMESTAMP This will update the time each instance you modify a row, sometimes very helpful for stored last edit info. This only works with timestamp, not datetime however. TIMESTAMP is always in UTC (i.e. elapsed seconds since 1970-01-01, in UTC), and your mySQL server auto-converts it to the date/time for the server timezone. In the long-term, TIMESTAMP is the way to go b/c you know your temporal data will always be in UTC. E.G. you won't screw your dates up if you migrate to a different server or if you change the timezone settings on your server.   I recommend using neither a DATETIME or a TIMESTAMP field. If you want to represent a specific day as a whole (like a birthday), then use a DATE type, but if you're being more specific than that, you're probably interested in recording an actual moment as opposed to a unit of time (day,week,month,year). Instead of using a DATETIME or TIMESTAMP, use a BIGINT, and simply store the number of milliseconds since the epoch (System.currentTimeMillis() if you're using Java). This has several advantages: You avoid vendor lock-in. Pretty much every database supports integers in the relatively similar fashion. Suppose you want to move to another database. Do you want to worry about the differences between MySQL's DATETIME values and how Oracle defines them? Even among different versions of MySQL, TIMESTAMPS have a different level of precision. It was only just recently that MySQL supported milliseconds in the timestamps. No timezone issues. There's been some insightful comments on here on what happens with timezones with the different data types. But is this common knowledge, and will your co-workers all take the time to learn it? On the other hand, it's pretty hard to mess up changing a BigINT into a java.util.Date. Using a BIGINT causes a lot of issues with timezones to fall by the wayside. No worries about ranges or precision. You don't have to worry about what being cut short by future date ranges (TIMEZONE only goes to 2038). Third-party tool integration. By using an integer, it's trivial for 3rd party tools (e.g. EclipseLink) to interface with the database. Not every third-party tool is going to have the same understanding of a "datetime" as MySQL does. Want to try and figure out in Hibernate whether you should use a java.sql.TimeStamp or java.util.Date object if you're using these custom data types? Using your base data types make's use with 3rd-party tools trivial. This issue is closely related how you should store a money value (i.e. $1.99) in a database. Should you use a Decimal, or the database's Money type, or worst of all a Double? All 3 of these options are terrible, for many of the same reasons listed above. The solution is to store the value of money in cents using BIGINT, and then convert cents to dollars when you display the value to the user. The database's job is to store data, and NOT to intrepret that data. All these fancy data-types you see in databases(especially Oracle) add little, and start you down the road to vendor lock-in. The major difference is a INDEX's on Timestamp - works a INDEX's on Datetime - Does not work Timestamp data type stores date and time but in UTC format, not in current zone format as datetime do. And when you fetch data, timestamp again convert that into current zone time. So suppose you are in USA and getting data from server which has time zone of USA, then you will get the date and time according to USA time zone. Timestamp data type column always get updated automatically when its row gets updated. So it can be useful to track when a particular row was updated last time. Beware of timestamp changing when you do a UPDATE statement on a table. If you have a table with columns 'Name' (varchar), 'Age' (int), and 'Date_Added' (timestamp) and you run the following DML statement UPDATE table SET age = 30 then every single value in your 'Date_Added' column would be changed to the current timestamp. I have always ignored timestamp data type while designing a database instead used datetime data type whenever I had to store date and time. Recently I read, what exactly timestamp data type is for and I think it is worth sharing.  So I’ll begin with the reason, why I never used timestamp! My perception was that both data types (timestamp and datetime) store date and time in database and are same. Now, why I am saying that we must also consider timestamp is because of following reasons: Timestamp data type stores date and time but in UTC format, not in current zone format as datetime do. And when you fetch data, timestamp again convert that into current zone time. So suppose you are in USA and getting data from server which has time zone of USA, then you will get the date and time according to USA time zone. Timestamp data type column always get updated automatically when its row gets updated. So it can be useful to track when a particular row was updated last time. These are the two main reasons I am aware of (apart from range and storage) and it is worth considering timestamp data type while designing the database. From my experiences If you want a date field in which insertion happens only once and u don't want to have any update or any other action on that particular field go with date time . For example in a user table REGISTRATION DATE field. In that user table if u want to know the last logged in time of a particular user go with a field of timestamp type so that field get updated. If you are creating the table from PHPMyAdmin default setting will update the timestamp field when row update happens. If your timestamp filed is not updating with row updation .