Search code examples
postgresqltime-and-attendance

PostgreSQL - Comparing time with value >24:00

I'm developing an attendance application using PHP with PostgreSQL as the database.

Here is my table:

  • ID - int, PK
  • Name - character varying(50)
  • In - time without time zone
  • Out - time without time zone

Basically, I want to check how many person are there in a specified time. For example, if the input is 08:00, then all person signed in before 08:00 and signed out after 08:00 will be counted.

The problem I encountered is when Out < In, which means the person signed in before 00:00 and signed out after 00:00. I tried to solve this problem by using the following SQL:

SELECT COUNT(*) FROM(
SELECT tbl_1."ID" FROM attendance as tbl_1 WHERE "In" < "Out" AND "In" < '$input' AND "Out" > '$input'
UNION 
SELECT tbl_2."ID" FROM attendance as tbl_2 WHERE "Out" < "In" AND "In" < '$input2' AND "Out" > '$input'
) AS tmp_tbl

$input is a PHP variable storing the input data (string). $input2 is a PHP variable storing the input data +24 hrs (I replaced the first two characters).

But unfortunately I encountered another problem - that PostgreSQL is unable to compare time with value >24:00.

Query failed: ERROR: date/time field value out of range: "35:11:00"

Can anyone help me out with this problem, other than adding another columns for DateIn and DateOut? storing information about the date?

Sorry for my english.

Solution

  • Well 35:11:00 isn't a valid time of the day.

    I guess your input value is something like 2013-06-01 35:11:00 which is an invalid timestamp (there are only 24 hours in a day) and therefor Postgres complains

    You will either have to do the calculation of the correct timestamp in PHP (so that the actual value passed to Postgres is 2013-06-02 11:11:00 or you can do that "inside" the SQL statement by splitting the value into a date part and an interval:

    Something like this:

    and "In" < date '2013-06-01' + interval '35:11:00' minute

    Where the actualy values would come from your PHP variables (but split up into two parts).

    Edit

    I missed the column definition being time (I somehow read timestamp).

    Apparently you are not storing a "time" (which is actually a "time of the day"), but a duration which can be represented using the interval data type.

    If you change your table definition from time to interval you can use your input like this:

    and "In" < interval '35:11:00' minute