Thread: 'now' in 7.0

Hi,

sorry if I miss something - there are so many changes in current
development and I didn't track them thoroughly in mailing list.

I've tried to port my db scheme which works with 6.5 to 7.0 and
got little problem:

create view www_auth as select  a.account as user_name, a.password, b.nick as 
group_name from users a, resources b, privilege_user_map c  where a.auth_id = c.auth_id and b.res_id = c.res_id and
(a.account_valid_untilis null or a.account_valid_until > datetime('now'::text))  and c.perm_id = 1;
 

ERROR:  No such function 'datetime' with the specified attributes

I had to use datetime('now'::text) as a workaround of bug in 6.5.3.

I tried just datetime('now') but still have the same problem.

Does the above view will works with now() ? 


Another problem:

create table tt (i int4, a datetime default 'now');
doesn't works and I still need 
create table tt (i int4, a datetime default now());

We have discussed this problem some time ago.
Regards,
    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Oleg Bartunov <oleg@sai.msu.su> writes:
> create view www_auth as select  a.account as user_name, a.password, b.nick as 
> group_name from users a, resources b, privilege_user_map c
>    where a.auth_id = c.auth_id and b.res_id = c.res_id and 
>    (a.account_valid_until is null or a.account_valid_until > datetime('now'::text))
>    and c.perm_id = 1;

> ERROR:  No such function 'datetime' with the specified attributes

Apparently the datetime() function got renamed to timestamp() in the
recent consolidation of date/time types.  I'd actually recommend that
you write CURRENT_TIMESTAMP, which is the SQL-approved notation...

> Does the above view will works with now() ? 

That should work too.

> Another problem:
> create table tt (i int4, a datetime default 'now');
> doesn't works and I still need 
> create table tt (i int4, a datetime default now());

?  Works for me:

regression=# create table tt (i int4, a datetime default 'now');
CREATE
regression=# insert into tt values(1);
INSERT 653163 1
regression=# insert into tt values(1);
INSERT 653164 1
regression=# select * from tt;i |           a
---+------------------------1 | 2000-02-22 17:15:16-051 | 2000-02-22 17:15:18-05
(2 rows)

although here also I think now() or CURRENT_TIMESTAMP would be safer
coding.
        regards, tom lane

> > ERROR:  No such function 'datetime' with the specified attributes
> Apparently the datetime() function got renamed to timestamp() in the
> recent consolidation of date/time types.  I'd actually recommend that
> you write CURRENT_TIMESTAMP, which is the SQL-approved notation...
> although here also I think now() or CURRENT_TIMESTAMP would be safer
> coding.

Right. We stayed away from recommending anything to do with
"timestamp" in the past because it was such a brain-damaged
implementation. 

Sorry for the porting effort; I could imagine someone working on a
"datetime compatibility package" which would define some of these
older functions. It would not need any compiled code, just a set of
CREATE FUNCTION definitions to hook up the new code with the old
names, something possible with Tom Lane's decoupling of entry points
from names...

If you are interested in doing this Oleg I'm sure we could slip it
into the beta tarball...
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California