Re: [GENERAL] Date math
On Sun, Jun 28, 2009 at 2:52 PM, Joe Conway m...@joeconway.com wrote: Adam Rich wrote: Hello, I have a table with a DATE field birth_date. The data obviously contains various dates in the past, such as 07/04/1970. In my query, I need to retrieve the person's next birthday. In other words, for the example date 07/04/1970, the query should return 07/04/2009 for the current week, but after this July 4th, it would return 07/04/2010. Ultimately, I need to find people with next birthdays within a certain range. If all you want to do is check to see if someone's birthday is coming up within a set period, then this query will return true or false: SELECT extract(doy FROM TIMESTAMP '1970-07-20') BETWEEN extract(doy FROM now()) AND extract( doy FROM CURRENT_DATE +30) Guy Flaherty [] []
Re: [GENERAL] Date math
Adam Rich wrote: Hello, I have a table with a DATE field birth_date. The data obviously contains various dates in the past, such as 07/04/1970. In my query, I need to retrieve the person's next birthday. In other words, for the example date 07/04/1970, the query should return 07/04/2009 for the current week, but after this July 4th, it would return 07/04/2010. Ultimately, I need to find people with next birthdays within a certain range. The best I've come up with so far is: snip This seems to work for most cases, but fails for Feb 29 birthdates. And converting dates to strings and back again seems like a hack... Is there a better way? (I prefer to treat 02/29 as 03/01 for non-leap years) Is there a way to add just enough years to birth_date to bring the result into the future? Maybe something like this? create table foo (f1 int, f2 timestamp); insert into foo values (1, '07/04/1970'); insert into foo values (2, '1976-02-29'); insert into foo values (3, '1962-06-27'); insert into foo values (4, '1981-06-26'); insert into foo values (5, '1991-07-26'); create or replace function next_birthday(timestamp) returns timestamp as $$ select case when now() - (extract(year from now()) - extract(year from $1))* '1 year'::interval $1 then $1 + (1 + extract(year from now()) - extract(year from $1)) * '1 year'::interval else $1 + (extract(year from now()) - extract(year from $1))* '1 year'::interval end as next_birthday $$ language sql; select now()::date as right_now, f2 as real_brithday, next_birthday(f2) from foo; right_now |real_brithday|next_birthday +-+- 2009-06-27 | 1970-07-04 00:00:00 | 2009-07-04 00:00:00 2009-06-27 | 1976-02-29 00:00:00 | 2010-02-28 00:00:00 2009-06-27 | 1962-06-27 00:00:00 | 2010-06-27 00:00:00 2009-06-27 | 1981-06-26 00:00:00 | 2010-06-26 00:00:00 2009-06-27 | 1991-07-26 00:00:00 | 2009-07-26 00:00:00 (5 rows) Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date math question
On Wed, Nov 26, 2008 at 11:54:33AM -0700, Kevin Kempter wrote: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; select ts, ts + dursec * '1 second'::interval ... depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date math question
On 26/11/2008 18:54, Kevin Kempter wrote: I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec Something like this? - select ts, ts + (dursec * interval '1 second') Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date math question
Kevin Kempter wrote: Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; Anyone know the correct syntax for this ? Try ts + dursec * '1 second'::interval Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date Math
am Mon, dem 07.05.2007, um 9:43:50 -0700 mailte Rich Shepard folgendes: From table (Permits) I want to identify those which expire within a specified time from today. For example: SELECT permit_nbr, title, date_issued, term, process_time from Permits WHERE (date_issued + term YEARS) (CURRENT_DATE + process_time MONTHS); Should I use TODAY rather than CURRENT_DATE? Do I need to cast intervals explicitly from seconds to days, months, or years? I'm not sure if I understand you correctly, if not, sorry. I think, you should cast your intervals, an example: select current_date + '10 months'::interval; You syntax above are wrong. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Date Math
-- Original message -- From: Rich Shepard [EMAIL PROTECTED] I've read both Section 8.5 of the on-line 8.2.4 docs, and the pertinent sections of Douglas Douglas, and I'm still not certain that I'm correctly expressing the query I want. Please correct as needed. From table (Permits) I want to identify those which expire within a specified time from today. For example: SELECT permit_nbr, title, date_issued, term, process_time from Permits WHERE (date_issued + term YEARS) (CURRENT_DATE + process_time MONTHS); Should I use TODAY rather than CURRENT_DATE? Do I need to cast intervals explicitly from seconds to days, months, or years? Are you thinking something like the following- test= select '01/01/04'::date +interval '3 year',current_date + interval '2 month'; ?column? | ?column? -+- 2007-01-01 00:00:00 | 2007-07-07 00:00:00 (1 row) test= select '01/01/04'::date +interval '3 year'current_date + interval '2 month'; ?column? -- t (1 row) Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
On Mon, 7 May 2007, A. Kretschmer wrote: I think, you should cast your intervals, an example: select current_date + '10 months'::interval; Andreas, OK. I wasn't clear on this point. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Date Math
On Mon, 7 May 2007, [EMAIL PROTECTED] wrote: test= select '01/01/04'::date +interval '3 year',current_date + interval '2 month'; ?column? | ?column? -+- 2007-01-01 00:00:00 | 2007-07-07 00:00:00 (1 row) Adrian, I think so, but without explicit strings. The dates and intervals are in the table, and I want the rows that meet the specified conditions. Is the following closer to correct? SELECT ... FROM Permits WHERE (date_issued::DATE + INTERVAL term) (CURRENT_DATE + INTERVAL process_time + INTERVAL '2 week') Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
-- Original message -- From: Rich Shepard [EMAIL PROTECTED] On Mon, 7 May 2007, [EMAIL PROTECTED] wrote: test= select '01/01/04'::date +interval '3 year',current_date + interval '2 month'; ?column? | ?column? -+- 2007-01-01 00:00:00 | 2007-07-07 00:00:00 (1 row) Adrian, I think so, but without explicit strings. The dates and intervals are in the table, and I want the rows that meet the specified conditions. Is the following closer to correct? SELECT ... FROM Permits WHERE (date_issued::DATE + INTERVAL term) (CURRENT_DATE + INTERVAL process_time + INTERVAL '2 week') Thanks, Rich If term and process_time are stored as intervals then it will work. Also if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the INTERVAL . Is date_issued stored as a date? If so it would not need to be cast. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Adrian Klaver wrote: If term and process_time are stored as intervals then it will work. Also if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the INTERVAL . Is date_issued stored as a date? If so it would not need to be cast. Adrian, Here are the pertinent declarations in the DDL: date_issued DATE NOT NULL CONSTRAINT invalid_date CHECK (date_applied = date_issued), term SMALLINT DEFAULT 1 NOT NULL, -- in years processing_time DEFAULT 180 NOT NULL SMALLINT, -- in days Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Rich Shepard wrote: term SMALLINT DEFAULT 1 NOT NULL, -- in years processing_time DEFAULT 180 NOT NULL SMALLINT, -- in days I can change from SMALLINT to INT4 if that helps clarify the values as INTERVALs. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
On Monday 07 May 2007 10:56 am, Rich Shepard wrote: On Mon, 7 May 2007, Adrian Klaver wrote: If term and process_time are stored as intervals then it will work. Also if they are stored as INTERVALS you can do CURRENT_DATE+process_time. In other words not have to declare the INTERVAL . Is date_issued stored as a date? If so it would not need to be cast. Adrian, Here are the pertinent declarations in the DDL: date_issued DATE NOT NULL CONSTRAINT invalid_date CHECK (date_applied = date_issued), term SMALLINT DEFAULT 1 NOT NULL, -- in years processing_time DEFAULT 180 NOT NULL SMALLINT, -- in days Thanks, Rich With this setup you will have to use an explicit string- date_issued + INTERVAL term|| 'years'. This will involve constructing a string and passing it to INTERVAL. The alternative is to change the column types of term and processing_time to interval and store the interval period with the interval qty i.e '1 year' for term and '400 days' for processing time for example. This way the you can use the values directly without invoking INTERVAL. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
Adrian Klaver [EMAIL PROTECTED] writes: With this setup you will have to use an explicit string- date_issued + INTERVAL term|| 'years'. This will involve constructing a string and passing it to INTERVAL. No, that's a truly awful way to do it. The correct way is to use number times interval multiplication, eg date_issued + term * '1 year'::interval; This reduces to not much more than a floating-point multiply, whereas the other way involves string-forming and string-parsing. Plus you can easily use whatever multiplier you like, eg '7 days' if weeks strike your fancy. It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Date Math
On Monday 07 May 2007 12:00 pm, Tom Lane wrote: Adrian Klaver [EMAIL PROTECTED] writes: With this setup you will have to use an explicit string- date_issued + INTERVAL term|| 'years'. This will involve constructing a string and passing it to INTERVAL. No, that's a truly awful way to do it. The correct way is to use number times interval multiplication, eg date_issued + term * '1 year'::interval; This reduces to not much more than a floating-point multiply, whereas the other way involves string-forming and string-parsing. Plus you can easily use whatever multiplier you like, eg '7 days' if weeks strike your fancy. It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. regards, tom lane Yea, I realized the error of my ways after hitting send. An ounce of proof reading prevents a pound of oops. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Tom Lane wrote: No, that's a truly awful way to do it. The correct way is to use number times interval multiplication, eg date_issued + term * '1 year'::interval; This reduces to not much more than a floating-point multiply, whereas the other way involves string-forming and string-parsing. Plus you can easily use whatever multiplier you like, eg '7 days' if weeks strike your fancy. Thank you, Tom. This makes sense to me and I did not pick up on this in my readings. It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. No, we'll use whatever data type makes extracting rows the easiest and most efficient. I don't see 'interval' as a data type in the docs. Is it a single-quoted string? We can do converstions between the UI and storage (in both directions), so the type in the DDL can be whatever's best. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Date Math
I don't see 'interval' as a data type in the docs. Is it a single-quoted string? We can do converstions between the UI and storage (in both directions), so the type in the DDL can be whatever's best. It is shown as the 4th item on table 8-9: http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html Regards, Richard Broermsa Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Richard Broersma Jr wrote: It is shown as the 4th item on table 8-9: http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html D'oh! Of course I saw that, but it did not register with me. Thanks, Richard! Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Tom Lane wrote: It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. Columns 'term' and 'process_time' converted to intervals. Just to confirm my understanding of today's lesson: SELECT permit_nbr, title, date_issued, term, process_time from Permits WHERE date_issued + term) (CURRENT_DATE + process_time + '2 week'::INTERVAL); is now correct syntax and use? Thanks all, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Date Math
On Monday 07 May 2007 3:19 pm, Rich Shepard wrote: On Mon, 7 May 2007, Tom Lane wrote: It might be that converting those columns to interval is the best answer, depending on what other processing needs to be done with them. But if Rich wants to leave them as numbers, the above is the best way to convert them to intervals on-the-fly. Columns 'term' and 'process_time' converted to intervals. Just to confirm my understanding of today's lesson: SELECT permit_nbr, title, date_issued, term, process_time from Permits WHERE date_issued + term) (CURRENT_DATE + process_time + '2 week'::INTERVAL); is now correct syntax and use? Thanks all, Rich Yes. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Date Math
On Mon, 7 May 2007, Adrian Klaver wrote: Yes. Adrian, Whew! :-) Now I'm working on pulling dates from two tables and checking if they're in the current quarter. I see that I need SELECT EXTRACT FROM column_name, and I'm thinking how to incorporate this with the rest of the selection criteria. I'll probably be back on the list tomorrow. Thanks very much, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
