Page 2 of 2
Re: Working with times.... [PostgreSQL]
Posted: Tue Feb 03, 2009 6:20 pm
by elektromutant
Code: Select all
$query = "SELECT * FROM avail WHERE stid = '$sentut' AND dayname = '$weekday'";
$result = pg_query($conn, $query) or die("Error in query: $query. " .
pg_last_error($conn));
stid is the ID of the person who the appoints are made to see.
the weekday is the day of the week extracted from the date that the person has chosen to make an appointment.
In the avail table along with the start and end times there is also weekday you see.
Re: Working with times.... [PostgreSQL]
Posted: Tue Feb 03, 2009 6:30 pm
by VladSun
I'm not sure what you are trying to display, but take a look at the results of this query:
[sql]SELECT TIME WITHOUT TIME ZONE '00:00:00' + (s.a || ' minutes')::interval AS quarters, avail.starttime, avail.endtimeFROM generate_series(0,3600,15) AS s(a)INNER JOIN avail ON stid = '$sentut' AND dayname = '$weekday'WHERE quarters BETWEEN avail.starttime AND avail.endtime[/sql]
UNTESTED!
PS: Please, use [ php ] [ /php ] and [ sql ] [ /sql ] tags instead of [ code ] [ /code ] tags
Re: Working with times.... [PostgreSQL]
Posted: Tue Feb 03, 2009 6:44 pm
by elektromutant
I didn't know about all the tags, I will do from now.
The code didn't work, even with a bit of tweaking.
This error comes up..
Warning: pg_query() [function.pg-query.html]: Query failed: ERROR: column "quarters" does not exist LINE 1: ...ail ON stid = 'jane' AND dayname = 'monday' WHERE quarters B... ^ in /var/www/html/dal1/book2.php on line 29
Error in query: SELECT TIME WITHOUT TIME ZONE '00:00:00' + (s.a || ' minutes')::interval AS quarters, avail.starttime, avail.endtime FROM generate_series(0,3600,15) AS s(a) INNER JOIN avail ON stid = 'jane' AND dayname = 'monday' WHERE quarters BETWEEN avail.starttime AND avail.endtime;. ERROR: column "quarters" does not exist LINE 1: ...ail ON stid = 'jane' AND dayname = 'monday' WHERE quarters B... ^
Don't worry about it. The main problem was getting the darn thing to work. This is only for a prototype for now so the code isn't super important yet.
I will definately revert back to it at a later date and have a mess around with it to see if I can achieve a result. I will let you know if I manage to do so.