0

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.

Brian Sumali
  • 5
  • 1
  • 3
  • It seems you have problems with $input. – bksi Jun 04 '13 at 09:08
  • Do you require `in` and `out` be of type `time`? Would it be an option to use a `timestamp`, thus adding date information to your values? I would like that better than any workaround using pure `time` values. – Thilo Jun 04 '13 at 09:09
  • @Thilo Could you please give me some example of time comparison using timestamp? I can't have the input store fixed date. I need to -let's say- compare input with the whole table. – Brian Sumali Jun 04 '13 at 09:22
  • Avoid the `time` datatype. It's a pain for this reason among others. Store `timestamp with time zone` for real world times, or `interval` for time differences. – Craig Ringer Jun 04 '13 at 12:08

1 Answers1

4

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
  • Shouldn't the 'minute' be 'hour'? :-) – Denis de Bernardy Jun 04 '13 at 09:45
  • @Denis: now that you mention it... But it does work for me: http://sqlfiddle.com/#!12/d41d8/960 –  Jun 04 '13 at 09:52
  • Umm, my input only stores "hh:mm:ss" value and my table doesn't use timestamp - it stores only the time... Can I do sth like `and "In" < date "Out" + interval '35:11:00' minute` instead? – Brian Sumali Jun 04 '13 at 09:52
  • @BrianSumali: ah I missed that. But then I guess you will have to change the way you handle that "time". Sounds like you actually want an `interval` not a `time` column –  Jun 04 '13 at 09:57