91–100 of 128 posts
Fantastic article as usual. One correction: the literal for UTC 00:00:00 00:00:00 is 'allballs', not 'allsballs' as mentioned. I know this because it made me giggle when I first discovered it, and it subsequently became an immature joke around the office for a day or two afterwards.
We have found that it is more annoying to keep track of the behavior of the library and of a home-grown date dimension. In my organization, we tend to use a standardized pattern that can handle arbitrary calendars, even when we're dealing with standard calendars.
Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…
For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. And never store "all day" dates as a timestamp (ie datemidnight); timezone issues can easily produce off-by-one dates.
Basically, 'time' is not a single thing. You usually want to represent it the way your users think about it - and that isn't always like a unix timestamp (although it very often is).
Small question / nitpick, WHERE created_at >= now() - '1 week'::interval would mean in the last 7 days right? not last week? Did some work on this recently in mysql and had to resort to calculating this using strtotime('last week');
$ select '2016-03-31'::timestamp - '1 month'::interval;
> 2016-02-29 00:00:00
$ select '2016-03-31'::timestamp + '11 month'::interval;
> 2017-02-28 00:00:00
$ select '2016-02-29'::timestamp + '1 year'::interval;
> 2017-02-28 00:00:00date_trunc() is one way but to_char is even better as you can get the resulting output to something nicer. Doing: SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP); gives: 2017-06-05 00:00:00+00 vs: SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-WW"wk"'); gives: 2017-23wk
SELECT to_char(now(), 'IYYY-"W"IW');
$ SELECT to_char('2017-01-01'::date, 'IYYY-"W"IW');
> 2016-W52
$ SELECT to_char('2017-01-01'::date, 'YYYY-"W"WW');
> 2017-W01Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…
It is regrettable that APIs make it easy to create datetime objects without timezones, which is why sane people have moved to e.g. Joda Time and the libraries based on it.
Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…
The problem with Unix milliseconds is it's actually not always increasing thanks to leap seconds. A positive leap second will result in the fractional part of the last second of the day going up to .999..., then resetting to .000... over again. https://en.wikipedia.org/wiki/Unix_time#Leap_seconds
That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a displayable string, so timestamptz are always displayed with a timezone, but that info does not come from the stored value.
Timestamptz needs timezone information only for operations that would give different results in different timezones, e.g. display as string, extract the day part, add a 1-month interval (DST info needed), etc. Comparing two timestamptz however doesn't require any timezone info.
The difference between timestamp and timestamptz is not about what they store, but about how they behave.
Edit: In my experience, this is not always obvious because postgres uses the default timezone of the instance whenever it needs such info with timestamptz operations. Using an explicit timezone often requires convoluted code.
Earlier quoted context omitted.
What doesn't sound right? You might be saying the same thing. To store this appointment correctly, you store the timezone of the appointment (let's say EST) and the timestamp within that timezone (4pm). So the final version is 16:00 EST. You can store a UTC version as well, but you need the local timestamp and timezone first. If you only store the timezone with a UTC timestamp, when you convert back after DST shifts…
No, I mean you make the appointment for December 3rd at 10:00am in Eastern Time (ET), and then you convert it to UTC to put into the database. Your local->UTC conversion library looks at the date and notices that this occurs during Daylight Savings Time for ET, and uses the correct offset when converting. Unless the rules about DST change between now and that meeting this is completely correct, you don't need to stor…
You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]! If you're using Postgres right now and have any columns like start_* and end_* for anything (e.g numbers or dates), you need to stop what you are doing and use a range type. They are amazing. You can do things like a unique index that ensures there are no overlapping ranges, you can do efficient inclusion/exclusion ind…