Re: [GENERAL] Date math

On Sun, Jun 28, 2009 at 2:52 PM, Joe Conway 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

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

Re: Fwd: [GENERAL] Date math

Adam Rich wrote: > Guy Flaherty wrote: >> >> >> You could use the extract() function to calculate the day of year of the person's birthdate and then check if this number is within today's day of year and range of days you want to check for, for example, today's day of year + 30 days to be withi

Re: Fwd: [GENERAL] Date math

Guy Flaherty wrote: On Sun, Jun 28, 2009 at 1:52 PM, 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 "ne

Fwd: [GENERAL] Date math

On Sun, Jun 28, 2009 at 1:52 PM, 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/

[GENERAL] Date math

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

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: se

[GENERAL] Date math question

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 d

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? - sel

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: htt

[GENERAL] Date math question

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 d

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 , and I'm thinking how to incorporate this with the rest of the selection criteria. I'll

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

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

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

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/

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

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 w

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 inter

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 .

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.

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

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

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

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 Ecosyst

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

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

[GENERAL] Date Math

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 to