5 ms·

Sometimes PostgreSQL time calculation is just too clever

15 points by akirk 17y ago

akirk 17y ago
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 ;)
- moe 17y ago
  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'.
lsb 17y ago
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.)
- gchpaco 17y ago
  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.
gkelly 17y ago
The mx.dateTime python module exhibits this behavior too. It bit me hard.

15 points by akirk 17y ago

akirk 17y ago

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 ;)

moe 17y ago
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'.

lsb 17y ago

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.)

gchpaco 17y ago
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.

gkelly 17y ago

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