Working with times.... [PostgreSQL]
Moderator: General Moderators
-
elektromutant
- Forum Newbie
- Posts: 9
- Joined: Tue Feb 03, 2009 4:09 pm
Working with times.... [PostgreSQL]
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
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]
Tkae a look at
http://www.postgresql.org/docs/8.0/stat ... s-srf.html
http://www.postgresql.org/docs/8.0/stat ... s-srf.html
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]
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.
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.
Re: Working with times.... [PostgreSQL]
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]
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!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)
Re: Working with times.... [PostgreSQL]
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]
Ok, so I have been experimenting a bit more and here is what I have so far...VladSun wrote:Post your query.
Code: Select all
SELECT TIME WITHOUT TIME ZONE '$star' + s.a from generate_series(0,200,1) as s(a);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.
Re: Working with times.... [PostgreSQL]
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.
[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]
I realised that after I had posted it. My apologies.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 am rather new to PHP so I am on a steep learning curve!
Re: Working with times.... [PostgreSQL]
So I think you need:
http://www.postgresql.org/docs/8.0/stat ... s-srf.html
http://www.postgresql.org/docs/current/ ... tring.html
http://www.postgresql.org/docs/8.0/inte ... etime.html
http://www.postgresql.org/docs/8.0/stat ... s-srf.html
http://www.postgresql.org/docs/current/ ... tring.html
http://www.postgresql.org/docs/8.0/inte ... etime.html
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]
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!
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]
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
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
Re: Working with times.... [PostgreSQL]
No, it's anoying to deal with people that want copy-paste solution and don't want to learn ...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!
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]
That timestamp comment confused me, but it turns out it's correct in my database just my explanation of said database is incorrectVladSun wrote:No, it's anoying to deal with people that want copy-paste solution and don't want to learn ...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!
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]
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;
}
}
}
}Re: Working with times.... [PostgreSQL]
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)){)
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