Y.P.Y
Well-Known Member
سلام
این مطالب مقایسه هایی در مورد فیلد نوع Datetime در مقابل فیل نوع Timestamp کرده که خیلی خوبه شما هم ببینید:
(یکسری معایب و مزایاشونو گفتن که برای اثباتشون می تونید امتحان کنید)
[align=left]
TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.
Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed
DATETIME represents a date
TIMESTAMP represents a well defined point in time
queries with "native" datetime will not be cached
queries with timestamp - will be cached
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.
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
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.
DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
To get the current unix timestamp in php just do time(); and in MySQL do SELECT UNIX_TIMESTAMP();
In "bigger" database terms, tiemstamp has a couple of special-case triggers on it.
Timestamps are also lighter on the database and indexed faster.
The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_work' where DATETIMEunchanged.
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
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.
When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing
TIMESTAMP's ability to auto update itself based on the current time without the use of unnecessary triggers
I like UNIX timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations etc
[/align]
http://stackoverflow.com/questions/409286/datetime-vs-timestamp
این مطالب مقایسه هایی در مورد فیلد نوع Datetime در مقابل فیل نوع Timestamp کرده که خیلی خوبه شما هم ببینید:
(یکسری معایب و مزایاشونو گفتن که برای اثباتشون می تونید امتحان کنید)
[align=left]
TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.
Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed
DATETIME represents a date
TIMESTAMP represents a well defined point in time
queries with "native" datetime will not be cached
queries with timestamp - will be cached
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.
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
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.
DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
To get the current unix timestamp in php just do time(); and in MySQL do SELECT UNIX_TIMESTAMP();
In "bigger" database terms, tiemstamp has a couple of special-case triggers on it.
Timestamps are also lighter on the database and indexed faster.
The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_work' where DATETIMEunchanged.
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
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.
When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing
TIMESTAMP's ability to auto update itself based on the current time without the use of unnecessary triggers
I like UNIX timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations etc
[/align]
http://stackoverflow.com/questions/409286/datetime-vs-timestamp
