The page I am working on has a javascript function executed to print parts of the page. For some reason, printing in Safari, causes the window to somehow update. I say somehow, because it does not really refresh as in reload the page, but rather it starts the "rendering" of the page from start, i.e. scroll to top, flash animations start from 0, and so forth. The effect is reproduced by this fiddle: http://jsfiddle.net/fYmnB/ Clicking the print button and finishing or cancelling a print in Safari causes the screen to "go white" for a sec, which in my real website manifests itself as something "like" a reload. While running print button with, let's say, Firefox, just opens and closes the print dialogue without affecting the fiddle page in any way. Is there something with my way of calling the browsers print method that causes this, or how can it be explained - and preferably, avoided? P.S.: On my real site the same occurs with Chrome. In the ex
Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.
ReplyDeleteIf you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
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.)
ReplyDeleteBy default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described here: MySQL Server Time Zone Support
I always use DATETIME fields for anything other than row metadata (date created or modified).
ReplyDeleteAs mentioned in the MySQL documentation:
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 birthdays.
The main difference is that DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
ReplyDeleteSo it only matters when you have - or may in the future have - synchronized clusters across time zones.
In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.
A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones.
Once I was having a conversation in which the time said I was replying to messages before the message was actually sent.
This of course could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.
I make this decision on a semantic base.
ReplyDeleteI use a timestamp when I need to record a (more or less) fixed point in time. For example when a record was inserted into the database or when some user action took place.
I use a datetime field when the date/time can be set and changed arbitrarily. For example when a user can save later change appointments.
TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.
ReplyDeletehttp://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
But like scronide said it does have a lower limit of the year 1970. It's great for anything that might happen in the future though ;)
mysql> show variables like '%time_zone%';
ReplyDelete+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone | Asia/Calcutta |
+------------------+---------------------+
2 rows in set (0.00 sec)
mysql> create table datedemo(
-> mydatetime datetime,
-> mytimestamp timestamp
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into datedemo values ((now()),(now()));
Query OK, 1 row affected (0.02 sec)
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone="america/new_york";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)
The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_work' where DATETIMEis unchanged.
I've converted my answer into article so more people can find this useful.
http://www.tech-recipes.com/rx/22599/mysql-datetime-vs-timestamp-data-type/
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.
ReplyDeleteIn "bigger" database terms, tiemstamp has a couple of special-case triggers on it.
What the right one is depends entirely on what you want to do.
Depends on application, really.
ReplyDeleteConsider setting a timestamp by a user to a server in New York, for an appointment in Sanghai. Now when the user connects in Sanghai, he accesses the same appointment timestamp from a mirrored server in Tokyo. He will see the appointment in Tokyo time, offset from the original New York time.
So for values that represent user time like an appointment or a schedule, datetime is better. It allows the user to control the exact date and time desired, regardless of the server settings. The set time is the set time, not affected by the server's time zone, the user's time zone, or by changes in the way daylight savings time is calculated (yes it does change).
On the other hand, for values that represent system time like payment transactions, table modifications or logging, always use timestamps. The system will not be affected by moving the server to another time zone, or when comparing between servers in different timezones.
Timestamps are also lighter on the database and indexed faster.
I would always use a unix timestamp when working with MySQL and PHP. The main reason for this being the the default date method in php uses a timestamp as the parameter so there would be no parsing needed.
ReplyDeleteTo get the current unix timestamp in php just do time(); and in MySQL do SELECT UNIX_TIMESTAMP();
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.
ReplyDeleteI prefer using timestamp so to keep everything in one common raw format and format the data in PHP code or in your SQL query. There are instances where it comes in handy in your code to keep everything in plain seconds.
ReplyDeleteI always use a UNIX timestamp, simply to maintain sanity when dealing with a lot of datetime info, especially when performing adjustments for timezones, adding/subtracting dates, and the like. When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing (Whether it be application code or database queries) in that you make use of light weight arithmetic rather then heavier date-time add/subtract functions.
ReplyDeleteNot 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
ReplyDeleteon 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.
I'm not sure it it's already been answered, but I found unsurpassed usefulness in TIMESTAMP's ability to auto update itself based on the current time without the use of unnecessary triggers. That's just me though, although TIMESTAMP is UTC like it was said, it can keep track across different timezones, so if you need to display a relative time for instance, UTC time is what you would want.
ReplyDeleteFrom my experiences If you want a date field in which insertion happens only once and u don't want o have update or any other action on that particular field go with date time .
ReplyDeleteFor example in a user table REGISTRATION DATE filed.
In that user table if u want to know the last logged in time of a particular user go with a filed of timestamp type let that filed updated with a trigger.
I like UNIX timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations etc. Then convert the result to Date in whatever format. This code finds out how much time in minutes passed between a timestamp from a document, and the current time.
ReplyDelete$date = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now - $result) / 60);
$min = round($unix_diff_min);