June 28, 200917 yr [RANT] WHY Does it have to operate the completely useless and ridiculous American Datestamp?! WHY!?! Year - Month - Day?! THAT'S JUST PLAIN STUPID! The British one is far more supreme and practical. Day - Month - Year a sensible sequence. ARGH! [/RANT]
June 28, 200917 yr [RANT] WHY Does it have to operate the completely useless and ridiculous American Datestamp?! WHY!?! Year - Month - Day?! THAT'S JUST PLAIN STUPID! The British one is far more supreme and practical. Day - Month - Year a sensible sequence. ARGH! [/RANT] I store all my dates in UNIX time format, do it and you wont regret it.
June 28, 200917 yr The American format would be Month-Day-Year. The format you are talking about is actually the most logical. The full form is: Year-Month-Day Hour:Minute:Seconds.Milliseconds In other words, it works from the largest unit to the smallest reading left to right: which is exactly how all our other Western numerics are formulated (thousands hundreds tens units . fractional). The British way would be: Day-Month-Year Hour:Minute:Seconds.Milliseconds which means reading left-to-right from a smallish unit to the largest then back to an even smaller one down to the finest. So that'd be why this format was chosen: it's also true that alphabetical sort is identical to numerical sort which would not be true in any other format like the US or British styles. I don't really see the problem however: you should be using the DATE_FORMAT function or similar to present a date in the format you want anyway.
June 28, 200917 yr I store all my dates in UNIX time format, do it and you wont regret it. TIMESTAMP fields are 3 bytes wide so are compact for currently typical date ranges ('1970-01-01 00:00:01' to '2038-01-09 03:14:07' UTC). DATETIMEs are 4 bytes but can hold a much larger range of values ('1000-01-01 00:00:00' to '9999-12-31 23:59:59' timezone independent). So which you use really depends on what you want/need to achieve.
June 28, 200917 yr TIMESTAMP fields are 3 bytes wide so are compact for currently typical date ranges ('1970-01-01 00:00:01' to '2038-01-09 03:14:07' UTC). DATETIMEs are 4 bytes but can hold a much larger range of values ('1000-01-01 00:00:00' to '9999-12-31 23:59:59' timezone independent). So which you use really depends on what you want/need to achieve. I was referring to the possibility to manipulate the stored data easily without converting it first. Also 2038 is a long way off, I hope any implementations of my code will be updated by 2038
June 28, 200917 yr I was referring to the possibility to manipulate the stored data easily without converting it first. Also 2038 is a long way off, I hope any implementations of my code will be updated by 2038 Yes, TIMESTAMPs are good for most things, and as you say it's easy to do numerical calculations with them (like add 60 for a minute, or 86400 for a day) and they obviously resist timezone changes too since they're in UTC by default. But it's not hard to do it with DATE_ADD() and DATE_SUB() either, or just convert the field with UNIX_TIMESTAMP() before doing the manipulations. 2038 is a long way off. But if you want to store historical data, DATETIME makes more sense for 1066-10-14 (Battle of Hastings) or 1918-11-11 11:00:00 (end of WW1) for example. Just some random thoughts there
June 28, 200917 yr Author I already know and understand why, I also understand full well the use of DATE_FORMAT since I am a PHP developer. I was just venting anger at the fact a client has asked me to alter their "faulty" system only to find they'd been using two different date formats one in the database (Y-M-D) and another one (D-M-Y) in their applications without conversion leading to conflicts, problems and errors and wondering why it wasn't working. So now i've got to fix it all. Which is irritating! It maybe more logical to read largest-smallest (however, it should be noted both Y-M-D and M-D-Y are both predominant dating methods in the US regardless) but to unexperienced individuals in the UK who are used to our date format (like my client) it causes problems, and I like our date format! heh But I also hate Americans (well the ones i'm related to anyway) lol! So that could just be a personal vendetta. It might of been wise to rant about how stupid my clients first developer was. Thanks for the input anyway
June 29, 200917 yr It's really not hard to convert though :S $query = mysql_query(SELECT *,UNIX_TIMESTAMP(timestamp) AS unix_time_stamp FROM table WHERE 1=1"); $result = mysql_fetch_array($query); echo date('d-m-Y',$result['unix_time_stamp']);
June 29, 200917 yr Why do American's have to be so different? Without us they wouldn't be here so they should be abide.
Create an account or sign in to comment