I'm new to Postgres and looking for some clarity on the timestamp data type. Can someone explain how it works? Specifically, I want to know if there's a way to set a timestamp column to automatically fill in when a new record is created, much like how an ID column works. Also, how can I update a record to the current timestamp? Additionally, does Postgres allow sorting by timestamp values? Thanks for any insights!
2 Answers
My approach is to always use timezone-aware timestamps and store them in UTC. It helps to manage conversions on the frontend based on each user's timezone preferences. This way, when you generate reports or printables, they can be displayed in the user's selected timezone. For further reading on handling date and time effectively, check out the resource I found: https://gist.github.com/timvisee/fcda9bbdff88d45cc9061606b4b923ca.
To automatically populate a timestamp column when a new record is created, you can use the following SQL command: `createdAt timestamp default current_timestamp`. Just make sure you also have an `updatedAt` column to track any changes afterward, since the `createdAt` should remain unchanged. For sorting by timestamps, you can easily use a query like `SELECT * FROM tablename ORDER BY timecolumnname`. This will retrieve your records in the order of their timestamps.
But isn't that a bit contradictory? By storing in a `timestamptz`, you lose control over the timezone when writing the data since it will reflect the session's timezone when accessed.