From: | Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> |
---|---|
To: | Neil Zanella <nzanella(at)cs(dot)mun(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: incrementing and decrementing dates by day increments programmatically |
Date: | 2003-10-27 17:14:50 |
Message-ID: | 20031027171449.GA24272@dcc.uchile.cl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Oct 27, 2003 at 01:40:53AM -0800, Neil Zanella wrote:
> alvherre(at)dcc(dot)uchile(dot)cl (Alvaro Herrera) wrote in message
>
> > You can of course do
> > SELECT now() + CAST('5 day' AS interval);
>
> Perhaps I should get myself a copy of the relevant parts of the SQL 99
> standard. How would you do the above in standard SQL?
I think one standard way of doing the above would be
SELECT CURRENT_TIMESTAMP + CAST('5 day' AS interval);
Or, as pointed out by Tom Lane and someone else, if you don't need the
time part,
SELECT CURRENT_DATE + 5;
> > For the date -I format you can use something like
> > SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');
>
> I believe Oracle also has a to_char() function. Is this to_char() function
> part of standard SQL or is it just a coincidence that both DBMSs support
> such a function call? I wonder whether the PostgreSQL to_char()
> function is compatible with the Oracle one.
AFAIK the main motivation to create the to_char() function in the first
place was in fact Oracle compatibility. If you want to do such a thing
in a standard manner, you should probably do
SELECT EXTRACT(year FROM a) || '-' ||
EXTRACT(month FROM a) || '-' ||
EXTRACT(day FROM a)
FROM (SELECT CURRENT_DATE + 5 AS a) AS foo;
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/)
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff | 2003-10-27 17:20:18 | Re: Experience with PL/xx? |
Previous Message | Andrew Sullivan | 2003-10-27 17:14:19 | Re: Question about the Internals |