Thread: inverse of "day of year"
Is there a function that would give me the date for a given day of year? Something like the inverse of "EXTRACT(doy FROM date)"? -- 09:04:02 up 10 days, 13:35, 4 users, load average: 0.42, 0.29, 0.33 ----------------------------------------------------------------- Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral -----------------------------------------------------------------
When grilled further on (Fri, 19 Mar 2004 09:06:17 -0300), Martin Marques <martin@bugs.unl.edu.ar> confessed: > Is there a function that would give me the date for a given day of year? > > Something like the inverse of "EXTRACT(doy FROM date)"? > Something like: select date_trunc( 'year', now() ) + (extract( doy from now() ) - 1) * '1day'::interval; Later, Rob -- 06:46:54 up 8 days, 9:58, 2 users, load average: 2.00, 2.11, 2.09 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> Martin Marques <martin@bugs.unl.edu.ar> confessed:
>> Is there a function that would give me the date for a given day of year?
> Something like:
> select date_trunc( 'year', now() ) + (extract( doy from now() ) - 1) *
> '1day'::interval;
timestamp + interval arithmetic is likely to give you problems at
daylight savings boundaries, since '1day' will be taken as '24hours'.
A more reliable way to get (what I assume is) the desired result is
to use the date + integer operator:
select date_trunc('year', now())::date + (extract(doy from now()) - 1)::integer;
regards, tom lane
El Vie 19 Mar 2004 12:10, Tom Lane escribió: > Robert Creager <Robert_Creager@LogicalChaos.org> writes: > > Martin Marques <martin@bugs.unl.edu.ar> confessed: > >> Is there a function that would give me the date for a given day of year? > > > > Something like: > > > > select date_trunc( 'year', now() ) + (extract( doy from now() ) - 1) * > > '1day'::interval; > > timestamp + interval arithmetic is likely to give you problems at > daylight savings boundaries, since '1day' will be taken as '24hours'. Is there information on how other intervals are taken? I mean, how is '1 year' afected with the leap years? -- 08:30:01 up 13 days, 13:01, 3 users, load average: 2.04, 1.80, 1.04 ----------------------------------------------------------------- Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral -----------------------------------------------------------------
On Mon, Mar 22, 2004 at 08:32:32 -0300, Martin Marques <martin@bugs.unl.edu.ar> wrote: > > Is there information on how other intervals are taken? I mean, how is '1 year' > afected with the leap years? Intervals are stored as two components. One is absolute time difference, and the other is in months. '1 year' is equivalent to '12 months'. The documentation on how they work in corner cases (when added or subtracted from timestamp(tz)) is sparse. It isn't documented whether the part in months or the absolute time is added first or what timezone is used (for timestamptz) when adding the months part. The basic idea is that months are added by looking at the timestamp as date and time and adding the appropiate number of months to the date and then converting back to a timestamp. It isn't documented what happens when the day of the month is past the end of the new month, but it looks like the last day of new month is used. If you convert an interval to an absolute time (such as by extracting the epoch), then months are converted to 30 days. Again, I don't think this is documented.
El Lun 22 Mar 2004 09:50, escribió:
>
> Intervals are stored as two components. One is absolute time difference,
> and the other is in months. '1 year' is equivalent to '12 months'.
> The documentation on how they work in corner cases (when added or
> subtracted from timestamp(tz)) is sparse. It isn't documented whether the
> part in months or the absolute time is added first or what timezone is used
> (for
> timestamptz) when adding the months part.
> The basic idea is that months are added by looking at the timestamp
> as date and time and adding the appropiate number of months to the date
> and then converting back to a timestamp. It isn't documented what happens
> when the day of the month is past the end of the new month, but it looks
> like the last day of new month is used.
> If you convert an interval to an absolute time (such as by extracting the
> epoch), then months are converted to 30 days. Again, I don't think this
> is documented.
Any thoughts on how this could affect date manipulation?
mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
'years')::interval; ?column?
---------------------2005-02-28 00:00:00
AFAIKS with other dates this works OK. :-)
-- 10:11:02 up 13 days, 14:42, 4 users, load average: 0.17, 0.12, 0.16
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral
-----------------------------------------------------------------
On Mon, Mar 22, 2004 at 10:14:40 -0300, Martin Marques <martin@bugs.unl.edu.ar> wrote:
>
> Any thoughts on how this could affect date manipulation?
This is consistant with what I explained about the behavior when adding
a month results in a day in a month past the end of the new month.
What do you expect to have happen here?
> mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
> 'years')::interval;
> ?column?
> ---------------------
> 2005-02-28 00:00:00
>
> AFAIKS with other dates this works OK. :-)
The real issue with intervals is that how they work in unusual cases is
not documented. The behavior could change in a future version without
much fanfare.
If you have the option to handle the date manipulation in Perl
use the DateTime modules. Also see Date::Calc.
A considerable amount of effort has been expended dealing with all
the nitty-gritty of time manipulation.
Use those modules , get your new date or time interval and
feed that to the dbms with a straightforward inequality .
Don't try to do date arithmetic in sql if you can avoid it you'll
run afoul of something or other.
On Mon, 22 Mar 2004, Bruno Wolff III wrote:
> On Mon, Mar 22, 2004 at 10:14:40 -0300,
> Martin Marques <martin@bugs.unl.edu.ar> wrote:
> >
> > Any thoughts on how this could affect date manipulation?
>
> This is consistant with what I explained about the behavior when adding
> a month results in a day in a month past the end of the new month.
> What do you expect to have happen here?
>
> > mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
> > 'years')::interval;
> > ?column?
> > ---------------------
> > 2005-02-28 00:00:00
> >
> > AFAIKS with other dates this works OK. :-)
>
> The real issue with intervals is that how they work in unusual cases is
> not documented. The behavior could change in a future version without
> much fanfare.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
El Lun 22 Mar 2004 12:56, Dana Hudes escribió: > If you have the option to handle the date manipulation in Perl > use the DateTime modules. Also see Date::Calc. NO! Actualy what I'm doing is getting out of that (I'm using PHP's PEAR Date::Calc) by creating some nice SQL and PL/PgSQL functions in the DB server. -- 11:01:02 up 14 days, 15:32, 4 users, load average: 1.48, 1.11, 0.72 ----------------------------------------------------------------- Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral -----------------------------------------------------------------