Thread: Simplicity in time/date functions
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 ? -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il --------------------------
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 > like this: > > select current_date::TIMESTAMP - '28 days'::INTERVAL as date > > whow, is there a shorter way ? > > -------------------------- > Canaan Surfing Ltd. > Internet Service Providers > Ben-Nes Michael - Manager > Tel: 972-4-6991122 > http://sites.canaan.co.il > -------------------------- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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 ==========================================================================
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 >> like this: >> >> select current_date::TIMESTAMP - '28 days'::INTERVAL as date >> >> whow, is there a shorter way ? >> >> -------------------------- >> Canaan Surfing Ltd. >> Internet Service Providers >> Ben-Nes Michael - Manager >> Tel: 972-4-6991122 >> http://sites.canaan.co.il >> -------------------------- >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org >> > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly >
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: > 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 ? > > -------------------------- > Canaan Surfing Ltd. > Internet Service Providers > Ben-Nes Michael - Manager > Tel: 972-4-6991122 > http://sites.canaan.co.il > -------------------------- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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: > 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
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