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
[1] https://docs.oracle.com/cd/B28359_01/server.111/b28318/datat...
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.
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.
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.
But dates should never be stored with a timezone at all, except in extremely rare cases where a day for some reason specific to a timezone. But even then, it's much more likely that you are trying to specify a time range that happens to start and end on a certain day in a certain place.
Dates, almost by definition, are independent of timezone. January 1st, 2020 began at several different times in different timezones. It should be represented as a date stamp: 2020-01-01 without any timezone.
If you really are trying to represent the day that starts at 12:00 AM PST on January 1, 2020 and ends at 12:00 PM PST the same day, then you probably just want two timestamps representing those instants and not a date at all. But again, that would be quite rare. In most cases you would just want to store 2020-01-01.
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 ...
But being able to freely/quicly stand up database servers and quickly create/drop databases makes development and testing much simpler and more reliable.
Given the question: "How do you know that deploying this thing will work?"
- When it's quick/legal to stand up fresh servers and create databases, the answer can be "I tested it, just now, and it works." - Otherwise you end up in "I read through it and it looks good" or perhaps "We tried most of it on the test instance last week before the other team started using it."
I much prefer the former.
For Oracle, a "database" is the a server instance; you create the database when you install the software (without creating a "database" you don't actually have anything running). For postgres, a database is just a level of data organization/segregation.
In an oracle instance you only have a single database. The equivalent of the postgres "database" would be "user/schema" in Oracle.
Initial installation may not matter much but long maintenance windows directly lead to higher costs. If every patch requires a 60-90 minute downtime, you're going to pay for that in production deploy time.
And for service that's expected to work without interruptions, I think, there should be a way to switch to a secondary database, otherwise those promises are futile. And if there's a way to switch to a secondary database, long patch install time is not a big deal.
Because whether it's 1 minute or 60 minutes, it's interruption nonetheless.
_if_
Of course, those aren't the only reasons, and probably not the the main reasons, why big orgs use bloated proprietary crapware.