Simplicity in time/date functions

dev=> select current_date() - 28;
?column?
------------
2001-12-06

Works for 7.2b

Darren

Darren Ferguson
Software Engineer
Openband

On Thu, 3 Jan 2002, Ben-Nes Michael wrote:

7.1 didn't like (), but otherwise still works.

fbax=# select current_date - 28;
?column?
------------
2001-12-06

Frank

At 02:44 PM 1/3/02 -0500, Darren Ferguson wrote:

dev=> select current_date() - 28;
?column?
------------
2001-12-06

Works for 7.2b

Darren

Darren Ferguson
Software Engineer
Openband

On Thu, 3 Jan 2002, Ben-Nes Michael wrote:

Hi All

I was amazed that:
select current_date() - 28 dont work at postgresql :(

I checked here and there and found that in postgresql i need to do

something

On Thu, 3 Jan 2002, Ben-Nes Michael wrote:

Hi All

I was amazed that:
select current_date() - 28 dont work at postgresql :(

I checked here and there and found that in postgresql i need to do something
like this:

select current_date::TIMESTAMP - '28 days'::INTERVAL as date

whow, is there a shorter way ?

template1=# select now() - 28;
?column?
------------
12-06-2001
(1 row)

template1=#

That's with 7.1.2, as someone else pointed out your original way
works in 7.2.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Try:

processdata=> SELECT CURRENT_DATE - 28;
?column?
------------
2001-12-06
(1 row)

Thomas could probably explain why this is. I can't remember the
reasoning, I simply learned to stay away from these types of functions
(now(), current_date(), etc.).

Jason

"Ben-Nes Michael" <miki@canaan.co.il> writes:

Jason Earl <jason.earl@simplot.com> writes:

Try:
processdata=> SELECT CURRENT_DATE - 28;

Thomas could probably explain why this is.

Because the SQL92 spec says so.

CURRENT_DATE with empty parens *is* allowed by ODBC, apparently, and
for 7.2 Peter Eisentraut hacked the parser to accept it with or without
empty parens. Thomas was not happy with that, and wants to take it out
again in 7.3, but I'd prefer to see things left as-is. IMHO there's no
real good reason *not* to accept the empty parens, and we'll keep
getting this sort of question if we revert to the hard-line
SQL-spec-and-nothing-but approach.

regards, tom lane

So there you have it folks, I knew there was a logical explanation.
Thanks for clearing that up. And thanks for everything else that you
guys do as well.

I am also glad to know that my somewhat irrational feature of
CURRENT_DATE() was based at least somewhat in fact. I lurk on the
HACKERS list (mostly because it is so darn educational), but I never
can be sure if my prejudices have arisen from my own fevered
imagination or from something I read on the list. As far as I am
concerned, if one of the Core PostgreSQL hackers doesn't like a
particular grammar than it is more than enough reason for this mere
mortal to stay clear the heck away from it :). There's nothing worse
than having to edit SQL statements that have been working fine for 6
months just because you added a superfluous set of ()'s.

Thanks again,
Jason

Tom Lane <tgl@sss.pgh.pa.us> writes:

On 3 Jan 2002, Jason Earl wrote:

Try:

processdata=> SELECT CURRENT_DATE - 28;
?column?
------------
2001-12-06
(1 row)

Thank you. I have a totally trival view that I was making overly
complex. This fixes it.
Point to note is the use of 'interval' gives the same 'problem'
formating.

SELECT CURRENT_DATE - interval '28 days';
?column?
------------------------
2001-12-06 00:00:00-08
(1 row)

Cheers,
Rod
--
Let Accuracy Triumph Over Victory

Zetetic Institute
"David's Sling"
Marc Stiegler