Re: simple calculation frustrations
On Fri, 3 Nov 2006, Zefram wrote: Dave Rolsky wrote: Pretty much all DBMS's (ok, not SQLite) have support for datetime as a data type. Worms. Can thereof. A datetime type is not a primary feature of a DBMS, so it's probably not had much attention paid to it. Until recently, MySQL's datetime type allowed dates such as 2006-04-31. Even if it's implemented correctly, it'll implement some arbitrarily selected date model that's not particularly likely to match your need. In this case, timezone behaviour is the issue, and not very many datetime types handle multiple timezones in the way desired. Clearly, MySQL is not the gold standard for this sort of thing. It's more like a tin standard ;) Postgre handles datetime type nicely, and supports real time zones. I still think that using the built-in date and datetime types is a better choice than breaking it down into components and storing those. After all, MySQL wouldn't validate your week number either. On an OT-note, this is why a DBMS should have good support for creating your own types, though AFAIK none really do. Using a DBMS's datetime type also loses you portability, of course. There's another can of worms. My answer to that is I generally don't care. If I pick a tool I'm picking it because I want to make use of it. Programming in Perl loses portability too, because I need to have Perl (and probably a minimum version) installed too. Anyway, this rather OT. -dave /*=== VegGuide.Orgwww.BookIRead.com Your guide to all that's veg. My book blog ===*/
RE: simple calculation frustrations
Matthew wrote: Ahh. And we have customers all over the world. Dang. See, all of this programming was done back shortly after we sprang forward so we didn't think about it. Any suggestions on how I should store a customers timezone in database? I think the best way is probably using the Olson time zone name. These are names like 'America/New_York' and 'Australia/Melbourne'. http://www.twinsun.com/tz/tz-link.htm Perl's DateTime module uses these time zones, as do MySQL = 4.1.3 and Oracle = 9i. Regards, Philip
Re: simple calculation frustrations
Thanks for the link Philip. I downloaded and updated all my /usr/share/zoneinfo then imported them into MySQL using their mysql_tzinfo_to_sql tool. Is there a better way to store the time zone of our customers other than VARCHAR? I noticed that the utility above created an INT indexed table of tzID - tzName but those tables are in the mysql database and I'd rather not give users permission to that table. Plus that would complicate SQL queries further by adding in either A) another JOIN statement or B) an extra SELECT to convert the INT to a name. Any thoughts? Thanks, Matthew Garrett, Philip (MAN-Corporate) wrote: Matthew wrote: Ahh. And we have customers all over the world. Dang. See, all of this programming was done back shortly after we sprang forward so we didn't think about it. Any suggestions on how I should store a customers timezone in database? I think the best way is probably using the Olson time zone name. These are names like 'America/New_York' and 'Australia/Melbourne'. http://www.twinsun.com/tz/tz-link.htm Perl's DateTime module uses these time zones, as do MySQL = 4.1.3 and Oracle = 9i. Regards, Philip
Re: simple calculation frustrations
On Fri, 3 Nov 2006, Zefram wrote: OK, so you want the other behaviour. I suggest that instead of storing an actual timestamp you should store a broken-down time: week number, day of week, local time of day, and (most important) timezone rule. You can put those together in DateTime to find out what point in time they refer to. Increment the week number as required. I don't think that's the best way to do it. Pretty much all DBMS's (ok, not SQLite) have support for datetime as a data type. I strongly recommend you convert all datetimes to the UTC time zone, then storing that value. Then when you pull it out, convert it back to the user's preferred time zone, which you can store as a string (America/Chicago). The advantage to this is that your datetime data is stored in a consistent way, so if you ever need to compare datetimes for some reason, you know that they'll compare sanely. Some DBMS's will let you store a datetime with a timezone, in which case you can do that and store them in many different time zones, and stuff still just works. In that case either solution is fine. I don't think MySQL supports that though. -dave /*=== VegGuide.Orgwww.BookIRead.com Your guide to all that's veg. My book blog ===*/
Re: simple calculation frustrations
Dave Rolsky wrote: Pretty much all DBMS's (ok, not SQLite) have support for datetime as a data type. Worms. Can thereof. A datetime type is not a primary feature of a DBMS, so it's probably not had much attention paid to it. Until recently, MySQL's datetime type allowed dates such as 2006-04-31. Even if it's implemented correctly, it'll implement some arbitrarily selected date model that's not particularly likely to match your need. In this case, timezone behaviour is the issue, and not very many datetime types handle multiple timezones in the way desired. Using a DBMS's datetime type also loses you portability, of course. The advantage to this is that your datetime data is stored in a consistent way, so if you ever need to compare datetimes for some reason, you know that they'll compare sanely. I think what he wants to store isn't actually a date, as in a point in time. Looks like he wants to store a time of week, timezone-relative, along with a timezone spec. The incrementing loop he came in with is just answering the question when will that time of week next occur?. -zefram
Re: simple calculation frustrations
To clarify, this is all in reference to our event system. A customer can create an event for March 3, 2006 as a one time event or a weekly recurring event. We are storing '2006-03-03' in the database as the base date. In order to find the date of the next event, we take the base date from DB and add to that the increment value until the new base date is greater than today's date: $baseDate = from_db(); while($baseDate $today) $baseDate += increment -Matthew Zefram wrote: Dave Rolsky wrote: Pretty much all DBMS's (ok, not SQLite) have support for datetime as a data type. Worms. Can thereof. A datetime type is not a primary feature of a DBMS, so it's probably not had much attention paid to it. Until recently, MySQL's datetime type allowed dates such as 2006-04-31. Even if it's implemented correctly, it'll implement some arbitrarily selected date model that's not particularly likely to match your need. In this case, timezone behaviour is the issue, and not very many datetime types handle multiple timezones in the way desired. Using a DBMS's datetime type also loses you portability, of course. The advantage to this is that your datetime data is stored in a consistent way, so if you ever need to compare datetimes for some reason, you know that they'll compare sanely. I think what he wants to store isn't actually a date, as in a point in time. Looks like he wants to store a time of week, timezone-relative, along with a timezone spec. The incrementing loop he came in with is just answering the question when will that time of week next occur?. -zefram
Re: simple calculation frustrations
Matthew wrote: hey guys, I can't believe I'm having this much trouble doing this. I'm pulling a UNIX_TIMESTAMP from MySQL and all I want to do is add a certain interval to that date then spit out the new date. Right now we only support weekly intervals in our app. Best bet: Get mysql to add the week, providing it's time zone is set correctly .. SELECT UNIX_TIMESTAMP( FROM_UNIXTIME( theField ) + INTERVAL 7 DAY ) (You're supposed to be able to do 1 WEEK too, but I can't get that to work in 4.1) Alternately, use DateTime: use DateTime; my $dt = DateTime-from_epoch( epoch = $eventDate, # So that DST is accounted for: time_zone = 'Australia/Melbourne', ); $dt-add( weeks = 1 ); $dt-epoch == (1 week after the $eventDate expressed as an epoch) Cheers! Rick Measham
Re: simple calculation frustrations
On Thu, 2 Nov 2006, Matthew wrote: I tried to do so. The problem is that the date's are stored in the users local timezone and we only store the offset from GMT. (ex: -6 or 7) Well, there's your problem right there. You realy can't do this and expect it to work, as you're finding out. You need to store the user's timezone, not their current offset. If you know all your users are in the US you might be able to come up with some hacks to deal with DST, but otherwise you really need to let DT::TimeZone do this for you (or MySQL, or something). Is there some array or table I can use to convert '-6' to something DateTime:: can use? No, there's no way to go from offset to time zone. Again, if your users are all in the US, _and_ no one's in a weird zone (like Indiana, parts of Arizona, etc) then you can probably convert it back. -dave /*=== VegGuide.Orgwww.BookIRead.com Your guide to all that's veg. My book blog ===*/
Re: simple calculation frustrations
Matthew wrote: Alternately, use DateTime: I tried to do so. The problem is that the date's are stored in the users local timezone and we only store the offset from GMT. (ex: -6 or 7) Is there some array or table I can use to convert '-6' to something DateTime:: can use? Epochs are UTC on most systems, so you're probably storing them as UTC .. -6 is meaningless and so useless for DST calculations. Last week Melbourne, Sydney and Brisbane were all +1000. Now Melbourne and Sydney are +1100, but Brisbane is still +1000. And Hobart switched from +1000 to +1100 a few weeks earlier. Unless you have more information that '-6' then you'll never be able to properly convert across a DST change. You could, of course, continue to use the UTC time zone. But you still can't properly turn that into the user's local time zone as it's no longer -6, but -7 or some such. Cheers! Rick Measham
Re: simple calculation frustrations
Ahh. And we have customers all over the world. Dang. See, all of this programming was done back shortly after we sprang forward so we didn't think about it. Any suggestions on how I should store a customers timezone in database? Thanks, Matthew Dave Rolsky wrote: On Thu, 2 Nov 2006, Matthew wrote: I tried to do so. The problem is that the date's are stored in the users local timezone and we only store the offset from GMT. (ex: -6 or 7) Well, there's your problem right there. You realy can't do this and expect it to work, as you're finding out. You need to store the user's timezone, not their current offset. If you know all your users are in the US you might be able to come up with some hacks to deal with DST, but otherwise you really need to let DT::TimeZone do this for you (or MySQL, or something). Is there some array or table I can use to convert '-6' to something DateTime:: can use? No, there's no way to go from offset to time zone. Again, if your users are all in the US, _and_ no one's in a weird zone (like Indiana, parts of Arizona, etc) then you can probably convert it back. -dave /*=== VegGuide.Orgwww.BookIRead.com Your guide to all that's veg. My book blog ===*/
