2

I implemented a country/region/city database for my site. Each city has a timezoneoffset stored as Varchar(32) in the format -06:00 , +09:00 etc. Now I'm not quite sure how to convert my UTC times which are stored in the database with those specific offsets. I know it's possible to convert using TimeZoneInfo.FindSystemTimeZoneById but that's not possible with the 'semi numeric' values I have.

Does anyone have an idea on how to go around this?

Thank you!

Mark
  • 49
  • 3

2 Answers2

3

If you've got offsets, then you want DateTimeOffset - you don't really need a time zone. On the other hand, you should be aware that what you've got won't accurately reflect local time, because a single offset is inadequate for most places due to daylight saving time.

I would suggest you store an actual time zone ID... and if you definitely want to store a single offset instead, I suggest you store the number of minutes in an integer field - there's no point in introducing a string representation which you just have to parse again.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • It was a little extra work but worth it I guess. I now added a new table with TimeZone Id's and connected them to the User record instead of City. Thank you for the guidance! – Mark Apr 03 '11 at 21:36
1

You could use TimeZoneInfo.CreateCustomTimeZone() for this:

DateTime utcTime = DateTime.UtcNow;
TimeZoneInfo targetTimeZone = TimeZoneInfo.CreateCustomTimeZone("MyId", TimeSpan.FromHours(-6), "Somewhere", "Somewhere");
DateTime targetTime = TimeZoneInfo.ConvertTime(utcTime, targetTimeZone);
BrokenGlass
  • 158,293
  • 28
  • 286
  • 335
  • If you know the amount of time to add, why bother with a time zone at all? Either use `DateTimeOffset`, or `DateTime.AddHours`. – Jon Skeet Apr 03 '11 at 18:55
  • @Jon Skeet: afaik when you use DateTimeOffset the passed DateTime and the utc offset have to match - so you couldn't create a Utc date time and pass in a different offset. But yes, `DateTime.AddHours()` is probably the easiest for OP. Sometimes you don't see the wood for the trees ;-) – BrokenGlass Apr 03 '11 at 19:02
  • No, a DateTime doesn't *have* a UTC offset in it. It's effectively timezone-less... calling ToUniversalTime or ToLocalTime assumes the system time zone, but that's not part of the value. – Jon Skeet Apr 03 '11 at 19:18
  • @Jon Skeet: You are right of course - the problem is just with the specific constructor I used for testing `DateTimeOffset` which validates the offset by comparing with `TimeZoneInfo.Local.GetUtcOffset(datetime)` - this just makes it awkward to work with unless you pass in ticks. – BrokenGlass Apr 03 '11 at 19:28
  • Ick, I wasn't aware of that constructor problem... that's nasty :( Dates and times are a bit of a mess... – Jon Skeet Apr 03 '11 at 19:29