Thread: 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 add7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (which Ihave done in the past).. but I hate dealing with month/year roll overs.. Travis
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 andadd 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (whichI 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 andadd 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (whichI 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: >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 andadd 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (whichI 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 > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
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 andadd 7 days to it and get the correct date.. or subtract 5 days.. or anything like that.. I can do it all in perl (whichI 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.