So, I found out why we had to switch all of our datetimes to timestamps: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
"The current time zone setting does not affect values in DATE, TIME, or DATETIME columns." So it appears this is a catch 22... In order to store things in UTC and then use the user's local TZ, you need to use 'timestamps'. But then you can't use them for years past 2038... However 'datetime' will store any date, but you can't store in UTC and display via the TZ setting. :-\ > -----Original Message----- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Monday, August 27, 2007 6:09 PM > To: 'MySQL General' > Cc: 'Chris' > Subject: RE: BUG: DATE_ADD 99999 fails, but 9999 works. > > > -----Original Message----- > > From: Chris [mailto:[EMAIL PROTECTED] > > Sent: Monday, August 27, 2007 5:45 PM > > > > I don't think this is a bug. I think what's happening is that your > > timestamp column can't hold that date, it's max value is > > somewhere in 2038. > > You appear to be correct, burried in the plethora of bullet > points here: > http://dev.mysql.com/doc/refman/5.0/en/datetime.html > "For example, TIMESTAMP values cannot be earlier than 1970 or > later than > 2038." > > So that _is_ the root cause of the problem, but it's still a bug. > > There is no reason (from a mySQL user/PHP developer's > perspective) that > 2038 should be my upper year limit. I should be able to make > any date up > to "9999-12-31" !!? > > This is absurd. We're making enterprise level tools that run at US > Government offices, The entire state of Alaska, Military, Colleges, > Fortune 500 companies.... You mean in 21 years from now, all this will > just fail miserably because of some obscure 2038 limitation? > This is Y2K > all over again -- unless mySQL fixes this bug. > > > So I guess either change your timestamp column to a > datetime column, > > Interesting thing with that, we used to use datetime columns (where > applicable) but since we store everything in UTC now, as this > product is > international, we had to switch (painfully I might add) to > timestamp. I > forget the exact reason, and this was about a year ago, so it may be > moot now anyways -- it had to do with using mySQL's > conversion routines > so the dates would display in the GUI for the user's local > timezone they > set in their profile, and those routines didn't work on > datetime or some > such nonsense. > > > or prevent users from putting invalid data in. > > I've limited the text field to 4 digits from 5. but that doesn't make > this any less of a mySQL "issue", that's just a band-aid to mask an > inadequacy of the RDBMS. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
