Handling Dates and Times in PostgreSQL Like a Hero
HTML-код
- Опубликовано: 6 сен 2024
- As programmers we need to work with dates and times, but we typically rely on languages and frameworks to handle all of this for us. This approach can work, until it comes to reporting and you're asked how you handled time zones, daylight savings, and location offsets!
The free Little SQL Book:
bigmachine.io/...
My Blog, with lots of Postgres videos:
robconery.com
This is awesome. My first time seeing generated columns
This channel absolutely deserves more subs. Hope you reach there one day. (don't forget me when you become famous lol).
Wow, thank you!
do you have a discord server ? or a community for similar interests people
I have a blog at robconery.com :) where I post all these things and more!
@@big-machine yees just what i neded. Thank u sir for this content
How to display the created_at column data in two seperate columns as date and time plz give reply for this comment plz 🙏
👍
This is incorrect. You changed the time being stored. If you have UTC in the database you need to always deal with it as UTC. If you were inserting records and specifying a time using the current time zone your code was wrong. Your SQL here changes the time that is stored in the row, so of course you get different results. If I store an int of 10 in one row and 10 minus 4 in another, those are different values. Look at your insert ststements, you are specifying an explicit time as a string without specifying the time zone! Your inserts are the problem / bad code. It has nothing to do with how the column is configured.
Time is always stored as UTC Postgres, no matter what you do. It's only when you ask for it in a query that it is projected, by default, to the system's date and time settings. Conversion would be impossible otherwise.
The docs are clear on this: "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client". Have a read here:
www.postgresql.org/docs/16/datatype-datetime.html
@@big-machine It doesn't matter how it is stored internally. You seem to be missing something. If you can't see the problem from your inserts I don't know how better to explain it. You misunderstand what is going on and you're saying it is one thing when it is not.
@@Me__Myself__and__I Let's try it this way: when you insert a date/time in Postgres, it's automatically converted to UTC and stored as such. A time zone is attached based on your DB's time zone setting (which is UTC if it's any cloud service) or, if you attach a time zone, it's stored with that.
The literal value stored on disk is *always* UTC. I think I'm pretty clear on this, and it's not just my opinion, this is verbatim in the docs.
There is still a ton of room for confusion, thus the video. I'm OK being wrong, any time, but you're going to have to do better than "if you can't see it...".
@@big-machine I started to type out a really long response. Then just to be certain I went and had a chat with ChatGPT to make sure I'm not crazy. it validated in a couple different ways what I said (resulting in about 5-6 pages of output). The jist is if you're handling your dates & times properly the timestamp vs timestamptz isn't going to make a difference. The exact same date & time gets stored & returned using default behavior if you're feeding in bare dates & times without time zone. If you want to discuss further I suggest you have a lengthy chat with ChatGPT.