How to get DateTime with a UTC datetime and a particular timezone?

Question

My data in SQL database looks like this:

PubDateUTC              PubDateUTCOffset
----------------------- --------------------
2011-08-04 10:02:50.000 +8:00:00
2012-04-23 02:32:25.287 +8:00:00
2010-09-26 04:23:00.000 +8:00:00

What I want is to get a DateTime based on PubDateUTC and PubDateUTCOffset, for example:

2011-08-04 10:02:50.000, +8:00:00 should result in 2011-08-04 18:02:50:000

I have tried with TimeZoneInfo class, but I don't know hot to create a instance of TimeZoneInfo with a string like "+8:00:00", which would be the CreateTimeZoneInfo method below

var tz = CreateTimeZoneInfo(post.PubDateUTCOffset);
return TimeZoneInfo.ConvertTimeFromUtc(post.PubDateUTC, tz);

Is there anyway to do this?

Note: I cannot change the data in SQL database.

Answer 1

You could try something like:

var date = post.PubDateUTC.Add(
                TimeSpan.Parse(post.PubDateUTCOffset.Replace("+", ""))
           );

The .Replace("+", "") is because TimeSpan will handle -01:00:00 but will choke on +01:00:00

Answer 2

I think you need to use DateTimeOffset class. This thread may be helpful.

http://msdn.microsoft.com/en-us/library/bb546101.aspx

Answer 3

This works, remove any leading "+" from the offset ( "-" are ok)

var d = new DateTimeOffset(DateTime.Parse("2011-08-04 10:02:50.000"), 
                           TimeSpan.Parse("08:00:00"));

• d.DateTime - the time in db = 10:02:50
• d.LocalDateTime - the time according to your servers timezone
• d.UtcDateTime - the time at GMT = 02:02:50

I'm not sure you want 18:02:50 since it is the time at GMT+16 (+16:00:00), unless of course that is how it's encoded in the db, then just ignore this post :)

Answer 4

You should change your post class to have one property:

public DateTimeOffset Published { get; set; }

Then when you read from the database (assuming you have datetime and varchar types in your database):

DateTime utc = DateTime.SpecifyKind(
                       (DateTime) reader["PubDateUTC"], DateTimeKind.Utc);

TimeSpan offset = TimeSpan.Parse(
                       ((string) reader["PubDateUTCOffset"]).Replace("+", ""))

post.Published = new DateTimeOffset(utc).ToOffset(offset);

Then when you need to consume it, you have all of the options of a full DateTimeOffset:

DateTime local = post.Published.DateTime;  // with your offset applied

DateTime utc = post.Published.UtcDateTime; // the original utc value

string s = post.Published.ToString("o");   //  2011-08-04T18:02:50.0000000+08:00