Thread: datetime default 'now' broken?

If I create a table with a datetime field with a default of 'now', every insert
the value is the time of table creation instead of the time of insert, which is how it behaved
in previous releases (I think this was even documented).

> If I create a table with a datetime field with a default of 'now',
> every insert the value is the time of table creation instead of the
> time of insert, which is how it behaved in previous releases (I think
> this was even documented).

I can't recall it ever working that way, though before we discovered
that it didn't we all assumed that it _did_ work that way :)

The workaround is to define it as

  ... default datetime('now'::text)

which forces the string to be evaluated at runtime. The SQL symbol
CURRENT_TIMESTAMP also misbehaves in "default" clauses, and I'm
considering changing it a bit to get around the problem.

                 - Tom

At 9:59 PM -0800 3/15/98, Thomas G. Lockhart wrote:
>> If I create a table with a datetime field with a default of 'now',
>> every insert the value is the time of table creation instead of the
>> time of insert, which is how it behaved in previous releases (I think
>> this was even documented).
>
>I can't recall it ever working that way, though before we discovered
>that it didn't we all assumed that it _did_ work that way :)
>

I'm running 6.1.1 and I *depend* on 'now' giving me the real now in an
insert.  I guess I'm glad I never upgraded.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

> >> If I create a table with a datetime field with a default of 'now',
> >> every insert the value is the time of table creation instead of the
> >> time of insert, which is how it behaved in previous releases (I
> >> think this was even documented).
> >
> >I can't recall it ever working that way, though before we discovered
> >that it didn't we all assumed that it _did_ work that way :)
>
> I'm running 6.1.1 and I *depend* on 'now' giving me the real now in an
> insert.  I guess I'm glad I never upgraded.

Hi Henry. You're missing a lot by not upgrading. DEFAULT clauses on
tables which Brett was asking about aren't even available pre-v6.2. The
'now' behavior in default clauses was an unexpected side-effect of the
optimizer, which evaluates things which look like constants before
execution.

Give me a call during the day at x47797 if you have any other
reservations about upgrading...

                   - Tom