4 ms·

Right but by that metric Oracle is archaic. Just look at Postgres[0] supported column data types and then look at Oracle[1]. Better data types means more tight

Right but by that metric Oracle is archaic. Just look at Postgres[0] supported column data types and then look at Oracle[1]. Better data types means more tightly defined columns, which means fewer bugs/less defensive programming/reduced maintenance/reduced code complexity. No doubt someone will be along shortly to tell me "you don't NEED it!" and then tell me how to hack constraints into making it act like something it is not, but I don't care. I have no interest in "forcing" Oracle to act like a modern database through repeatedly having to re-define 30+ year old common data types (e.g. Int32, Int64, Long, Bool, etc). As a developer I /hate/ working with Oracle. It is just cludgy. I don't care how many times people point to vague indefinables for why it is "superior," it sucks to work with. Microsoft Sql Server is better, and Postgres/Sqlite are superb. Just the fact Postgres has a date (with no-time) column means it outright wins. In Oracle, we stored them as <Date> 12:00 but that's a gotcha since it can get timezone adjusted in the pipeline (e.g. <Date> 12:00 becomes <Date> 08:00) and the result be subtly broken. Cannot time-zone adjust a real date type. [0] https://www.postgresql.org/docs/9.5/datatype.html https://www.postgresql.org/docs/9.5/datatype.html [1] https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT012 https://docs.oracle.com/cd/B28359_01/server.111/b28318/datat...

z3t4 6y ago
You should store dates with timezone or you can't make calculations like how old is this post. You could base it off system time, but then the calculation have to take into account summer/winter time.
- Someone1234 6y ago
  > You should store dates with timezone or you can't make calculations like how old is this post. I think you misread the post you responded to. It is discussing the benefit of Date Vs. DateTime data types. In Postgres, by design, a date has a 1 day resolution (i.e. no concept of hours/minutes, within that date). This is hugely useful for scenarios where you intentionally want and only want a 1 day resolution. It not being timezone adjustable is a feature, not a bug. Storing a DateTime with 12:00 and then an offset of e.g. 0, is a needlessly complicated work-around when a data type that doesn't even have the concept of hours or timezones exists.
- brianwawok 6y ago
  Everything in the DB should be UTC. Anything else is crazy.
  - growse 6y ago
    This isn't true. For example: If a user schedules an event to happen at 15:35 on July 29th 2021 IST, you cannot know with certainty what the equivalent UTC value is. You know what it would be assuming the relationship between IST and UTC stays the same between now and next year. If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. Timezones are political, but they're also how people organise their lives. The user's local timezone is context that you can't just discard.
    - deleted 6y ago
      [deleted]
    - int_19h 6y ago
      > If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. It is not wrong. When timezones change, the database that contains that info is updated - but it still retains information about historical rules, precisely so that earlier dates can still be converted reliably. On the other hand, if you use local times, then you are not able to distinguish between pre- and post-clock change, when the same time repeats twice.
      - tchaffee 6y ago
        How are you going to know to look for changes to IST when all you have is a UTC date?
      - deleted 6y ago
        [deleted]
      - growse 6y ago
        You're not converting a historic date though, you're converting a future date. And that's an inherently uncertain process. It is literally impossible to know what UTC time 2021-07-29T15:35:00 IST refers to until it happens. The Indian government is free to redefine IST as they wish, and could choose to do that at 15:34:59.
- raphaelj 6y ago
  Some times it does not make sense to associate a time component to a date, just because it happens on a day, not at a certain time. For example, in a lot of countries, Labor Day is on May 1, Christmas on Dec 25. These do not happen at a certain time, and there is no timezone to be associated with these events. If I'm requesting some user's birthday, I'm going to store it as a date. I don't have a time component. I don't know the time zone. In some cases, dates are just dates ...
  - z3t4 6y ago
    For those born around new year the hour is important as it will decide what year they are born. It will also be important for birthdays, You don't want to send happy birthday on the wrong day - which can be different depending on what timezone they are in.

z3t4 6y ago

You should store dates with timezone or you can't make calculations like how old is this post. You could base it off system time, but then the calculation have to take into account summer/winter time.

Someone1234 6y ago
> You should store dates with timezone or you can't make calculations like how old is this post. I think you misread the post you responded to. It is discussing the benefit of Date Vs. DateTime data types. In Postgres, by design, a date has a 1 day resolution (i.e. no concept of hours/minutes, within that date). This is hugely useful for scenarios where you intentionally want and only want a 1 day resolution. It not being timezone adjustable is a feature, not a bug. Storing a DateTime with 12:00 and then an offset of e.g. 0, is a needlessly complicated work-around when a data type that doesn't even have the concept of hours or timezones exists.
brianwawok 6y ago
Everything in the DB should be UTC. Anything else is crazy.
- growse 6y ago
  This isn't true. For example: If a user schedules an event to happen at 15:35 on July 29th 2021 IST, you cannot know with certainty what the equivalent UTC value is. You know what it would be assuming the relationship between IST and UTC stays the same between now and next year. If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. Timezones are political, but they're also how people organise their lives. The user's local timezone is context that you can't just discard.
  - deleted 6y ago
    [deleted]
  - int_19h 6y ago
    > If India suddenly decides to implement some sort of DST, or make any other changes to the definition to IST, the UTC value you stored is now wrong. It is not wrong. When timezones change, the database that contains that info is updated - but it still retains information about historical rules, precisely so that earlier dates can still be converted reliably. On the other hand, if you use local times, then you are not able to distinguish between pre- and post-clock change, when the same time repeats twice.
    - tchaffee 6y ago
      How are you going to know to look for changes to IST when all you have is a UTC date?
    - deleted 6y ago
      [deleted]
    - growse 6y ago
      You're not converting a historic date though, you're converting a future date. And that's an inherently uncertain process. It is literally impossible to know what UTC time 2021-07-29T15:35:00 IST refers to until it happens. The Indian government is free to redefine IST as they wish, and could choose to do that at 15:34:59.
raphaelj 6y ago
Some times it does not make sense to associate a time component to a date, just because it happens on a day, not at a certain time. For example, in a lot of countries, Labor Day is on May 1, Christmas on Dec 25. These do not happen at a certain time, and there is no timezone to be associated with these events. If I'm requesting some user's birthday, I'm going to store it as a date. I don't have a time component. I don't know the time zone. In some cases, dates are just dates ...
- z3t4 6y ago
  For those born around new year the hour is important as it will decide what year they are born. It will also be important for birthdays, You don't want to send happy birthday on the wrong day - which can be different depending on what timezone they are in.