pelago

UPDATED February 19th, 2010: As BobM pointed out, the original solution to this problem didn’t account for fractional decimals. Originally I didn’t include them because Intervals didn’t require that level of precision, but apparently fractional decimals are quite common elsewhere. Because of that, I’ve updated this post, along with the regex, to include support for fractional decimals.

For the Intervals API, we’re wrestling with issues surrounding data input validation. This recently became interesting when the matter of date validation came up. Ordinarily, Intervals allows many, many different date formats, dependent on the locale that the customer is using (for example, Intervals may expect the date format ‘mm/dd/yyyy’ for US customers, ‘dd.mm.yy’ for a customer in Austria).

For our API developers, we wanted to use a common, universal format, one that would be easily compatible with our application and database layers. For that we selected ISO 8601, which is great in terms of widespread use, but not so great in terms of how complicated its specifications are.

Generally, ISO 8601 looks something like ‘2009-05-20′ for dates and ‘2009-05-20 12:30:30′ for date/time combinations. These two examples encompass 98% of the user input we’re likely to encounter. But we wanted to make sure that if we told developers they could use ISO 8601 dates, our system would support it. (more…)

The new year always brings with it a few small things that go bump in the morning. 2008 was no different. Intervals started behaving oddly on New Year’s Eve morning — the default timesheet was a year behind schedule. What happened?

In our PHP code, we are using the ISO-8601 week number of year, as specified on the PHP date function page, but we weren’t using ISO-8601 for the year. The ISO-8601 week number specifies the last monday of a year as the first week of the new year, if that new year begins before thursday. Intervals thought it was the first year of 2007!

In PHP, the fix was as easy as converting all instances of date(‘Y’) to date(‘o’), according to php.net:

ISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead. (added in PHP 5.1.0)

That fixed everything on the PHP side of things. But next we had to dig into the SQL queries and get them to use the ISO Year.

Snag.

PostgreSQL 8.2.5 doesn’t support ISO Year in the Extract function. EXTRACT(ISOYEAR, timestamp) is being included in PostgreSQL 8.3, as specified here in the RC1 documentation. But PostgreSQL 8.3 hasn’t been released yet, and we needed to fix things immediately.

Our final PostgreSQL fix was to instead use the TO_CHAR(timestamp, ‘IYYY’) function. It’s not ideal to be using a string formatting function for data comparisons, because it slows down some of the queries. But we had to trade some performance to get things working properly again in the new year. As soon as the PostgreSQL developers release a stable version of 8.3, we’ll change our queries back to using EXTRACT(ISOYEAR, timestamp).