i was writing a query where you have tickets in a table. The column first_use_on represents the TIMESTAMP of the first time it has been used. first_use_on is by the default NULL and then updated once used the first time only. My query works fine but now i got the need to know outside of the query if the runned query fired first_use_on, so i thought about of adding first_use_on = NOW() AS is_first_usage in the RETURNING. Can i be 100% sure that the NOW() compared in the returning is always the very same of the one used in the UPDATE part? Could there be some cases where they differ?
UPDATE
l_codes
SET first_use_on = (
CASE WHEN first_use_on IS NULL THEN
NOW()
ELSE
first_use_on
END )
WHERE
l_code = 'C9TCH' AND id_mostra = 1
RETURNING
first_use_on,
first_use_on = NOW() AS is_first_usage,
NOW() > DATE_TRUNC('day', first_use_on + INTERVAL '1 DAY') AS expired,
DATE_TRUNC('day', first_use_on + INTERVAL '1 DAY') AS expiration_on;
Yes. As specified in the documentation for now():
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.
The transaction start is constant for the entire statement.
Postgres: is NOW() used in more places of the query guaranteed to be always the same?
Yes. It returns the timestamp at the beginning of the current transaction, as explained in the documentation:
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.