0

This is in C#

I'm consuming data from an RSS feed and want to store the data in our database. The problem that I'm having is how to store the LastUpdateTime.

XmlReader reader = XmlReader.Create(uri);
SyndicationFeed rssFeed = SyndicationFeed.Load(reader);

var lastUpdate = rssFeed.LastUpdatedTime;

The problem is that rssFeed.LastUpdateTime is a DateTimeOffset. However, we're using SQL Server 2005, which only has DateTime. What is the best practice for handling this scenario? If it matters, all our databases and servers are on Eastern time.

Thanks.

John
  • 3,332
  • 5
  • 33
  • 55

2 Answers2

2

It would be simplest to just convert the DateTimeOffset to a UTC DateTime using rssFeed.LastUpdateTime.UtcDateTime and then store just that in the database in a DateTime field.

Don't bother storing the offset, there's no need. The Offset will not relate the DateTime value to any time zone anyway.

You can then use the built in TimeZoneInfo class in .NET to convert your UTC DateTime values to ANY time zone whatsoever. The beauty of the TimeZoneInfo class is that it will also adjust your DateTime values for Daylight Saving Time.

This would also be the best solution in terms of performance and storage.

PapillonUK
  • 642
  • 8
  • 20
1

I normally have an additional column, something like

[LastUpdateOffset] SMALLINT NOT NULL

and use that to store the offset for the given row. Then convert for the client, something like

/// <summary>
/// Convert a given UTC DateTime value into its localized value from given offset
/// </summary>
/// <param name="column">DateTime value, such as CreatedOn, UpdatedOn etc</param>
/// <param name="offset">-60 is DST for example</param>
private void ConvertUTCToClientTimeZone(DateTime column, int offset)
{
    TimeSpan offsetTimeSpan = new TimeSpan(0, offset, 0).Negate();
    DateTimeOffset newDate = new DateTimeOffset(column, offsetTimeSpan);
    column = newDate.DateTime;
}

Hope this helps! :)

Phil
  • 2,315
  • 2
  • 18
  • 26
  • OK, so to store it in the database, you're storing a UTC time, right? Something like DateTime dbDateTime = rssFeed.LastUpdateTime.UtcDateTime – John Jul 12 '12 at 19:53
  • Within your example, the offset parameter is defined as an int. There are a number of countries and regions within countries with UTC offsets that cannot be defined using int values. For example the time offset from UTC in India and Sri Lanka is +05:30. This is more common than one might expect. Often the decisions for the UTC offset for local time is based on politics. Left to the whim of politicians, the UTC offset isn't necessarily predictable. –  Jan 06 '16 at 17:27