Working with times.... [PostgreSQL]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

Working with times.... [PostgreSQL]

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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)
There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Post your query.
There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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.
Last edited by elektromutant on Tue Feb 03, 2009 5:30 pm, edited 2 times in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
elektromutant
Forum Newbie
Posts: 9
Joined: Tue Feb 03, 2009 4:09 pm

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

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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)){)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply