Page 1 of 2

Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 4:11 pm
by elektromutant
Hello all, I am in desperate need of guidance.

So, I have a start time and an end time stored in a postgresql database. This represents the availability of someone.

I need to take these values and create a list of available appointments (each 15 minutes long).

Eg.

Start time - 09:00
End time - 09:59

09:00
09:15
09:30
09:45

Initially I had these values set up as the Time data type, but I could not find a way to manipulate the value as I was planning on using a loop to keep adding 15 minutes to the time and then adding that to an array.

Is there a way to manipulate times stored in a time data type?

I also experiemented with storing them as real numbers eg 09.30 etc, but obviously when adding 15 minutes to each one it goes about .60 which is technically a new hour. I see no way around this as I don't think manual rounding is possible?

Does anyone have a solution or idea for me to look into as to how I would get this to work?

Thank you in advanced,
Daniel

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 4:20 pm
by VladSun

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 4:41 pm
by elektromutant
Thank you for the advice.

Did you mean to use this and use the real data type instead of time? I have tried executing this with time and I am failing. :P

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 4:50 pm
by VladSun
The example is very good :)
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 4:53 pm
by elektromutant
VladSun wrote:The example is very good :)
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
That's what I have used but I am having trouble. As the time I want to use is stored as 00:00:00. I am trying to figure out what input syntax it takes and having no luck!

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:03 pm
by VladSun
Post your query.

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:10 pm
by elektromutant
VladSun wrote:Post your query.
Ok, so I have been experimenting a bit more and here is what I have so far...

Code: Select all

SELECT TIME WITHOUT TIME ZONE '$star' + s.a from generate_series(0,200,1) as s(a);
$star contains 00:09:00.
The values at the end don't have anything to do with my application currently.

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:28 pm
by VladSun
The example is:
[sql]SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);[/sql]

and you have "rewritten" it as:
[sql]SELECT current_date FROM generate_series(0,14,7);[/sql]

Obviously you don't follow it.
Start with the query from the examples and modify it step by step to satisfy your needs.

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:29 pm
by elektromutant
VladSun wrote:The example is:
[sql]SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);[/sql]

and you have "rewritten" it as:
[sql]SELECT current_date FROM generate_series(0,14,7);[/sql]

Obviously you don't follow it.
Start with the query from the examples and modify it step by step to satisfy your needs.
I realised that after I had posted it. My apologies.

I am rather new to PHP so I am on a steep learning curve!

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:48 pm
by VladSun

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:49 pm
by elektromutant
I have a way to solve my problem without using the generate_series.

I am going to make use of this query and loop it until it equals the end time.

[sql]SELECT TIME WITHOUT TIME ZONE '$star' + interval '15 minutes'[/sql]

Thank you for all the guidance, it has helped me a lot. I know how annoying dealing with newbies can be, heh!

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:51 pm
by VladSun
Some notes:
00:09:00 - means 9 minutes past midnight ;)

You need to concatenate series values with a 'minutes' string in order to get an interval type. Then you should cast it to interval:

string_here::interval

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 5:53 pm
by VladSun
elektromutant wrote:I have a way to solve my problem without using the generate_series.

I am going to make use of this query and loop it until it equals the end time.

[sql]SELECT TIME WITHOUT TIME ZONE '$star' + interval '15 minutes'[/sql]

Thank you for all the guidance, it has helped me a lot. I know how annoying dealing with newbies can be, heh!
No, it's anoying to deal with people that want copy-paste solution and don't want to learn ...
I'm glad that you are not one of them

Anyway ... The query should look like that:
[sql]SELECT     TIME WITHOUT TIME ZONE '09:00:00'  + (s.a || ' minutes')::interval AS dates FROM     generate_series(0,120,15) AS s(a)[/sql]

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 6:11 pm
by elektromutant
VladSun wrote:
elektromutant wrote:I have a way to solve my problem without using the generate_series.

I am going to make use of this query and loop it until it equals the end time.

[sql]SELECT TIME WITHOUT TIME ZONE '$star' + interval '15 minutes'[/sql]

Thank you for all the guidance, it has helped me a lot. I know how annoying dealing with newbies can be, heh!
No, it's anoying to deal with people that want copy-paste solution and don't want to learn ...
I'm glad that you are not one of them

Anyway ... The query should look like that:
[sql]SELECT     TIME WITHOUT TIME ZONE '09:00:00'  + (s.a || ' minutes')::interval AS dates FROM     generate_series(0,120,15) AS s(a)[/sql]
That timestamp comment confused me, but it turns out it's correct in my database just my explanation of said database is incorrect :P

I have got some working code based on the post I made previously.

Here it is if you are interested.

Code: Select all

while($rows = pg_fetch_assoc($result)){
        $end = $rows['endtime'];
        $curr = $rows['starttime'];
        $ftimes[] = $curr;
        
        while($curr<$end){
            $query2 = "SELECT TIME WITHOUT TIME ZONE '$curr' + interval '15 minutes' AS apptime;";
            $result2 = pg_query($conn, $query2) or die("Error in query: $query2. " .
                pg_last_error($conn));
        
            while($rows2 = pg_fetch_assoc($result2)){
                $curr = $rows2['apptime'];
                if($curr<$end){
                    $ftimes[] = $curr;
                }
            }   
        }
}
The only thing I am not pleased with is the added if statement before it adds it to the array, but the way the code is structured meant it was the only way. Hey at least it works!

Re: Working with times.... [PostgreSQL]

Posted: Tue Feb 03, 2009 6:17 pm
by VladSun
You have nested queries, so I think you can use a JOIN query instead of all this code :)
What's the starting query (i.e. the one which is used by while($rows = pg_fetch_assoc($result)){)