Hacker Newsnew | past | comments | ask | show | jobs | submitlogin
Sometimes PostgreSQL time calculation is just too clever (lethargy.org)
15 points by akirk on June 17, 2009 | hide | past | favorite | 5 comments


When I was a child, I liked looking at those world maps with the timezones drawn in columns.

Today, as a programmer, I am still fascinated with time zones, but there is almost always something that I hadn't considered when dealing with time and time zones.

Actually the programmer's perspective made time zones even more fascinating to me ;)


     Our units of temporal measurement, from seconds on up to months,
     are so complicated, asymmetrical and disjunctive so as to make
     coherent mental reckoning in time all but impossible.  Indeed, had
     some tyrannical god contrived to enslave our minds to time, to
     make it all but impossible for us to escape subjection to sodden
     routines and unpleasant surprises, he could hardly have done
     better than handing down our present system.  It is like a set of
     trapezoidal building blocks, with no vertical or horizontal
     surfaces, like a language in which the simplest thought demands
     ornate constructions, useless particles and lengthy
     circumlocutions.  Unlike the more successful patterns of language
     and science, which enable us to face experience boldly or at least
     level-headedly, our system of temporal calculation silently and
     persistently encourages our terror of time.

     ...  It is as though architects had to measure length in feet,
     width in meters and height in ells; as though basic instruction
     manuals demanded a knowledge of five different languages.  It is
     no wonder then that we often look into our own immediate past or
     future, last Tuesday or a week from Sunday, with feelings of
     helpless confusion.  ...

     -- Robert Grudin, `Time and the Art of Living'.


I don't know how it works with other frameworks, but with ActiveSupport in Rails, when I want to say "30 days from now", it's "Time.now + 30.days", and Ruby does the math from days->seconds.

(IIRC, 1.month = 30.days in Rails.)


See, that's wrong in many circumstances. If I want January 12th, 2019 + 1 month, I expect to get Feb 12th, 2019, not Feb 11th. Also days does not convert well into seconds in UTC, because of leap seconds. Historical dates are even more of a mess--consider September 1752 in Britain and the US, or February 1918 in Russia. (Julian -> Gregorian calendar conversions that caused several days to wink out of existence--there is no September 13th, 1752 in Britain, although there is in France.) There is a February 30, 1712 in Sweden.

Calendrical calculations are imposing an artificial setup on a changing system, and have lots of weird anomalies as a result of this.


The mx.dateTime python module exhibits this behavior too. It bit me hard.




Consider applying for YC's Summer 2026 batch! Applications are open till May 4

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: