Postgres Now() Timestamp Doesn't Change, When Script Works

Postgres now() timestamp doesn't change, when script works

From TFM, highlights mine:

9.9.4. Current Date/Time

PostgreSQL provides a number of functions that return values related
to the current date and time. These SQL-standard functions all
return values based on the start time of the current transaction:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

...

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.

PostgreSQL also provides functions that return the start time of the
current statement, as well as the actual current time at the instant
the function is called. The complete list of non-SQL-standard time
functions is:

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
named to clearly reflect what it returns. statement_timestamp()
returns the start time of the current statement (more specifically,
the time of receipt of the latest command message from the client).
statement_timestamp() and transaction_timestamp() return the same
value during the first command of a transaction, but might differ
during subsequent commands. clock_timestamp() returns the actual
current time, and therefore its value changes even within a single SQL
command. timeofday() is a historical PostgreSQL function. Like
clock_timestamp(), it returns the actual current time, but as a
formatted text string rather than a timestamp with time zone value.
now() is a traditional PostgreSQL equivalent to transaction_timestamp().

Why does 'now()'::timestamp return the current timestamp in PostgreSQL?

It's a special function of the conversion from string to timestamp or date/time type; the parentheses are superfluous, as it's not really a function call. I have a pretty strong suspicion that that particular value was added in order to serve as the special default value for timestamp columns.

How do I automatically update a timestamp in PostgreSQL

To populate the column during insert, use a DEFAULT value:

CREATE TABLE users (
  id serial not null,
  firstname varchar(100),
  middlename varchar(100),
  lastname varchar(100),
  email varchar(200),
  timestamp timestamp default current_timestamp
)

Note that the value for that column can explicitly be overwritten by supplying a value in the INSERT statement. If you want to prevent that you do need a trigger.

You also need a trigger if you need to update that column whenever the row is updated (as mentioned by E.J. Brennan)

Note that using reserved words for column names is usually not a good idea. You should find a different name than timestamp

Now() without timezone

SELECT now()::timestamp;

The cast converts the timestamptz returned by now() to the corresponding timestamp in your time zone - defined by the timezone setting of the session. That's also how the standard SQL function LOCALTIMESTAMP is implemented in Postgres.

If you don't operate in multiple time zones, that works just fine. Else switch to timestamptz for added_at. The difference?

• Ignoring time zones altogether in Rails and PostgreSQL

BTW, this does exactly the same, just more noisy and expensive:

SELECT now() AT TIME ZONE current_setting('timezone');

postgres removes microseconds from now()

I bet updated_at is of type timestamp(0) so that it discards fractional seconds.

Postgres: Update date and retain time from timestamp

Try this:

UPDATE mytable 
SET field1 = '2015-12-31'::timestamp + 
             EXTRACT(HOUR FROM field1) * INTERVAL '1 HOUR' +
             EXTRACT(MINUTE FROM field1) * INTERVAL '1 MINUTE' +
             EXTRACT(SECOND FROM field1) * INTERVAL '1 SECOND' 
WHERE ...

Demo here

PL/pgSQL Loops don't update current timestamp / now()

current_timestamp is defined to be:

the start time of the current transaction, their values do not change during the transaction

you probably want to use clock_timestamp() instead which returns a value that changes within a transaction, see the link above for a more complete description.

---

Related Topics