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 .
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
