Azure Table Storage throws a StorageException when using DateTime.Min

1601 was the year that Pierre de Fermat and King Louis XIII of France were born, Robert Devereux was beheaded in the Tower of London, the Dutch defeated the Portuguese navy in the battle of Bantam Bay and the year Shakespear's Hamlet is thought to have been performed for the first time. Monday January, 1st 1601 (in the Gregorian calendar) was a pretty uneventful day yet the first day of the 17th century is affecting millions today. Why? Because Windows uses it as the base date to calculate timestamps and the current date by counting the number of ticks (aka jiffy) that have lapsed since that epoch date. In other words, in Windows, a date is represented by the number of 100 nanosecond intervals since 1601-01-01 (ISO 8601) and stored as a 64-bit integer value. All time values can then be converted to the more commonly used calendar dates, taking into account timezones, daylight saving (DST) and leap seconds. Interestingly the reference date corresponding to time zero and the tick value used vary between operating systems and languages.

s = second / ms = millisecond / ns = nanosecond

Side note on the Gregorian Calendar

The Gregorian calendar which was first adopted by Italy, Poland, Portugal and Spain, was introduced by Pope Gregory XIII (1502-1585) in 1582 to replace the Julian Calendar (named after the Roman Emperor Julius Caesar in 45BC). Britain only adopted it in 1752.

A regular Gregorian year consists of 365 days while a leap year which occurs every 4 years has 366 days with an intercalation or leap day added as February 29. One Gregorian calendar cycle consists of:

• 400 years
• 303 regular years
• 97 leap years (with 3 leap days omitted)
• 20,871 seven-day weeks
• 146,097 days

The length of each month can be calculated as follows:

L = 30 + ((M + floor(^(M)/₈)) MOD 2) - (M == 2 ? F : 0)

L = month length in days

M = month number 1 to 12

F = leap year ? 1 : 2 (Adjustment for February)

A leap year must satisfy the following rule:

1. The year is evenly divisible by 4
2. If the year can be evenly divided by 100, it is not a leap year, unless
3. The year is also evenly divisible by 400. Then it is a leap year.

Why 1601-01-01?

This date was first used by the American National Standards Institute (ANSI) for COBOL programming and by Windows subsequently. The rational behind it was that it made some datetime based calculations easier. As noted above the Gregorian calendar leap years occur every 4 years and years that are divisible by 100 are not leap years except when they are also divisible by 400. So when Windows was designed, 1600 was the century leap year available at the time (year 2000 being next and 2400 after that) and January 1, 1601 was therefore the first day of a new 400-year Gregorian calendar cycle. All dates and times in Windows have subsequently been calculated based on the number of 100 ns that have elapsed since hour 0 of that day.

How is this relevant to Azure table storage?

You might have guessed it by now. Azure table storage uses 1601-01-01 as its epoch date. I was first made aware of it when trying to save an entity with a System.DateTime property set to the default .NET value (i.e. 01-01-01) and a Microsoft.WindowsAzure.Storage.StorageException was thrown. I've therefore added a simple check to ensure that the application I'm working on doesn't attempt to save any 'invalid' dates:

Note that null values are valid in this context.

Found any inaccuracies in this post? Please leave a comment.

Pascal Arnould on Twitter

Sign up** to Azure Weekly to receive Azure related news and articles direct to your inbox or follow on Twitter: **@azureweekly

Links and Refs

• FOLDOC on-line computing dictionary
• Wikipedia - Epoch (reference date)
• Wikipedia - Notable epoch date in computing
• Wikipedia - System time
• Wikipedia - Orders of magnitude (time)
• EpochConverter
• MSDN - SQL Server datetime data type
• Tibor Karaszi's SQL Server pages - the ultimate guide to the datetime datatypes
• timeanddate.com - The Gregorian calendar
• daylight-saving-time.com - History of DST
• University of Cambridge Computer Laboratory -  A summary of the international standard date and time notation (ISO 8601)
• historyorb.com - Historical Events for Year 1601
• Macro History and World Timeline - 17th Century Timeline: 1601 to 1700
• Shakespeare on-line - Hamlet History
• The Elizabeth Files - Robert Devereux
• Birthday Checker - Born in 1601
• Why is the Win32 epoch January 1, 1601?