111–120 of 128 posts
I never see discussion of fiscal calendars in these sorts of threads and articles. Whenever I read these, all I see is something that would fall apart as soon as approximately half of my clients look at it. 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…
Earlier quoted context omitted.
Doesn't matter as far as the DB is concerned. You either just store UTC and co, or you store it just as an datetime + offset. 100% of apps in production don't handle most of the timezone intricacies anyway and the sky hasn't fallen (heck, the sky hasn't even fallen for Y2K).
Timezones have more information and are a mini-database in themselves, which is why most database include the information and have timezone capable types. Offsets as simple numbers are not usable in any real calculation. 100% of apps? You must not know what timezone intricacies are then or just how much effort is spent to make sure time itself is properly handled, especially in any major application that has global u…
You'd be surprised.
On the debate of "timestamp vs timestamptz" I reached the opposite conclusion of the author: I've got Amazon RDS instances set to UTC and my timestamps are stored as UTC times with no timezone awareness. Instead, I add the timezone while querying. I think this is better because I never have to remember anything about server settings!
I discovered that the `AT TIME ZONE` clause has two meanings, so I sometimes have to use it twice. In this example which selects all records created this month:
...WHERE create_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' > date_trunc('month',current_date)
I have this table:
CREATE TABLE all_dates ( date_stamp date NOT NULL, is_month_end boolean, is_year_end boolean, is_week_end boolean, is_quarter_end boolean, CONSTRAINT all_dates_pkey PRIMARY KEY (date_stamp) )
filled with data from 1st Jan 1980 to 31st Dec 2050, which is the range my application needs.
It's a mere 22k rows and has a whole host of uses.
Earlier quoted context omitted.
> If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.
Maybe here's an example that's more useful: Some years ago, I worked on an in-house IaaS platform that included several workflow modules for the sysadmins (alert dispatching, time-tracking, etc.). One of those components was a scheduler for one-time and recurring tasks. For example, when you delete a system, you set a timer for 14 days to have the system remind you (via e-mail or by issuing an alert into the queue) t…
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'…
However, you do need to do date arithmetic from time to time, whether using a wrapped epoch time in the database or in the application. "One day from now" turns out to be complicated enough that we delegate to libraries to get it right; and Postgres's implementation of these features is solid. When you want to `GROUP BY` day, for example, there are performance benefits to doing that on the database side -- and for analysts, there is often little alternative but to handle dates with DB provided functionality.
When it comes to date arithmetic, how do you handle that with UNIX timestamps?
I never see discussion of fiscal calendars in these sorts of threads and articles. Whenever I read these, all I see is something that would fall apart as soon as approximately half of my clients look at it. 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…
For those who want to implement this in Python, I've written a gist: https://gist.github.com/Dowwie/bec0a29bcd37eea41cde8d5188626...
Earlier quoted context omitted.
I would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. 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 La…
Unix timestamps are bound to UTC[1], so it would never be correct to have a timestamp represent number of seconds since 1970 _somewhere_. You would have to convert the timezone for any location you want it to be relative to. [1] https://en.m.wikipedia.org/wiki/Unix_time
If you store your event time as epoch time, you're storing "time of event in UTC" and "location of event". Which means every change of location requires you to recalculate and update the epoch time of the event. When users change Las Vegas to Chicago, you must update two fields - and if you ever screw it up, you have no way of knowing what the user originally intended.
This is a very bad case for epoch time. You may want to create an index on the calculated epoch time for queries, but store the datetime as localdatetime - the form that most accurately represents the source data.
You are of course free to ignore this advice and learn the hard way. Just stay off my lawn.
Earlier quoted context omitted.
I would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. 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 La…
> If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.
Your UI has a selector for Date/Time and a selector for Location. They might even be different screens. The Location may have been prefilled with a guess based on geoip. Or maybe they saved the wrong 'Springfield'.
The user took an action to change Location. If that has the side effect of changing Time, the user will be surprised.