New answers tagged timestamp
Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around: Time zone storage in PostgreSQL timestamps Ignoring timezones altogether in Rails and ...
You should always store data in it's native datatype so you can use the built-in functions. And the data type of a timestamp is obviously a timestamp. Btw, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation.
The reason is the different versions installed in the two environments. The option for DEFAULT CURRENT_TIMESTAMP for datetime columns was added in 5.6 version (specifically in 5.6.5). See the doumentation about the DATE, DATETIME, and TIMESTAMP Types: The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating ...
You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...
Top 50 recent answers are included