Endjin - Home

Azure Table Storage throws a StorageException when using DateTime.Min

by Pascal Arnould

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.

Platforms – Languages Tick Epoch date
Windows, NTFS, COBOL  100 ns  January 1, 1601
MS DOS  1 s  January 1, 1980
.NET  100 ns  January 1, 1
SQL Server  1/300 ms  January 1, 1753
Mac OS (up to version 9)  1 s  January 1, 1904
Mac OS X, Unix, Linux, C, Android, Java, JavaScript  1 s  January 1, 1970

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/8)) 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?

About the author