1

So I'm trying to query a time card table whose structure is like this

employeeId | clockInTime| clockOutTime
-----------+------------+--------------
   555     | 1462797450 | 1462785465 
   555     | 1462883850 | 1462871850 
   111     | 1463056650 | 1463044650 <== skip this
   555     | 1463143050 | 1463131050 <== get this
   555     | 1463229426 | 1463245655 <== but not this

What I'm trying to do is select all rows between two values but also the next row after that group of rows for that employee regardless of the value

This is my query

select "clockInTime", "clockOutTime", lead("clockInTime",1)
from "timeCard"
where "clockInTime" between 1462797450 and 1462883850
and "employeeId" = 555

but I get this error:

error: function lead(bigint, integer) does not exist

But when I remove the double quotes from lead() I only end up getting this because my column names are camelCase:

error: column "clockintime" does not exist

I'm using node.js and the node-pg client.

Darkrum
  • 1,325
  • 1
  • 10
  • 27

2 Answers2

2

You did not "remove the double quotes from lead()". The error message reveals that you actually removed the double quotes from "clockInTime":

error: column "clockintime" does not exist

Consider:

The long and the short of it: Don't use caMelCase identifiers with Postgres if you can avoid it:

As for the task you describe:

select all rows between two values but also the next row after that group of rows for that employee

The OVER clause was missing from the window function lead() like @Gordon pointed out. But even with the syntax error fixed, lead() (or any other window function) does not seem like the right approach to get what you ask for. It adds a column to every row in the result, while you want to add a row to the set.

I suggest UNION ALL and ORDER BY / LIMIT 1 to add the "next" row to the result set:

SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" BETWEEN 1462797450 AND 1462883850

UNION ALL
(  -- parentheses required
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" > 1462883850
ORDER  BY "clockInTime"
LIMIT  1
);

A multicolumn index on ("employeeId", "clockInTime") would make this very fast, even for big tables.

If "clockInTime" is not defined unique, you may want to add more expressions to ORDER BY to get a deterministic result in case of ties.

Parentheses are required to add LIMIT or ORDER BY to an individual leg of a UNION query. Example:

If you want the leading rows sorted as well:

(
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" BETWEEN 1462797450 AND 1462883850
ORDER  BY "clockInTime"
)
UNION ALL
(
SELECT *
FROM   "timeCard"
WHERE  "employeeId" = 555
AND    "clockInTime" > 1462883850
ORDER  BY "clockInTime"
LIMIT  1
);
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

You need the over clause. I'm not sure what logic you are looking for exactly, but something like:

select "clockInTime", "clockOutTime",
       lead("clockInTime", 1) over (order by clickInTime)
from "timeCard"
where "clockInTime" between 1462797450 and 1462883850 and "employeeId" = 555;

Typically, you would want this per employee:

select "clockInTime", "clockOutTime",
        lead("clockInTime", 1) over (partition by "employeeid" order by clickInTime)
from "timeCard"
where "clockInTime" between 1462797450 and 1462883850 and "employeeId" = 555;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786