Time manipulation..

On Tue, 12 Nov 2002, Williams, Travis L, NPONS wrote:

If I have a date/time column using timestamp.. how can I manipulate the date/time easily.. like take the date/time and add 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (which I have done in the past).. but I hate dealing with month/year roll overs..

Assuming you have a date field, you should be able to do something like
this:

select datefield + 7 from table where id=1;

to get the date plus 7 days.

I tested it this time, and it seems to work in 7.2.3 as well as 7.3 beta.

On Tue, 2002-11-12 at 14:35, Williams, Travis L, NPONS wrote:

If I have a date/time column using timestamp.. how can I manipulate the date/time easily.. like take the date/time and add 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (which I have done in the past).. but I hate dealing with month/year roll overs..

Travis

Well, you can easily do things like select now() + '7 days'::interval;
or select now() - '5 days'::interval; and if you need just a part of
the time use the date_part() function. check the docs for more info and
other suggestions.

Robert Treat

I have a related question...

Is it faster to set the time via now() during insert or let application
construct one, say a C++ app.
I'm thinking C++ is going to do a bunch of string processing to assemble
this time-stamp, then
PG is going to do some other string processing to convert it into an
internal binary format and
then store it. So perhaps its just faster to have now() set as the
default....

what would you say....

Robert Treat wrote:

Not sure about the postgresql solution, but any of perl's CPAN date modules
should handle month/year rollovers for you correctly. Time::Piece is my
current favorite, as it's very OO; Date::Calc works well also. There's no
need to reinvent the wheel. :-)

Wes Sheldahl

"Williams, Travis L, NPONS" <tlw@att.com>@postgresql.org on 11/12/2002
02:35:26 PM

Sent by: pgsql-general-owner@postgresql.org

To: <pgsql-general@postgresql.org>
cc:
Subject: [GENERAL] Time manipulation..

If I have a date/time column using timestamp.. how can I manipulate the
date/time easily.. like take the date/time and add 7 days to it and get the
correct date.. or subtract 5 days.. or anything like that.. I can do it all
in perl (which I have done in the past).. but I hate dealing with
month/year roll overs..

Travis

---------------------------(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

On Tue, Nov 12, 2002 at 14:35:26 -0500,
"Williams, Travis L, NPONS" <tlw@att.com> wrote:

If I have a date/time column using timestamp.. how can I manipulate the date/time easily.. like take the date/time and add 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (which I have done in the past).. but I hate dealing with month/year roll overs..

You can use intervals to do this. Intervals can specify a time difference
in years and months as well as days, hours, seconds, etc. The documentation
doesn't indicate which part is added first. A quick check indicates
that month, year gets added before day, hours, minutes, seconds, but
you might want to double check the code before relying on this.