CODEWITHSUNDEEP
postgresql
Ramesh Raghavan
Ramesh Raghavan

Reputation: 149

How to add time and time intervals in SQL queries

I want to check the late comers and early goers for a particular attendance table. My attendance table looks like this:

╔═════════════╦═════════════╦══════════╗
║ date_record ║ checkinTime ║ Emp_code ║
╠═════════════╬═════════════╬══════════╣
║ 2019-02-01  ║ 09:00:00    ║ p-01     ║
╠═════════════╬═════════════╬══════════╣
║ 2019-02-01  ║ 09:30:00    ║ p-02     ║
╠═════════════╬═════════════╬══════════╣
║ 2019-02-01  ║ 10:00:00    ║ p-03     ║
╠═════════════╬═════════════╬══════════╣
║ 2019-02-01  ║ 09:30:00    ║ p-04     ║
╚═════════════╩═════════════╩══════════╝

suppose the actual time is 09:00:00 and i want to show the employee code (Emp_code) who got late by 30 minutes. if i want to check the above like this:

select Emp_code from attendanceTable where checkinTime = ('09:00:00' + '00:30:00');

How can i do this in PostgreSQL?

Upvotes: 0

Views: 2592

Answers (2)

sandeep thawait
sandeep thawait

Reputation: 1

I suppose better query would be :

select * from attendancetable WHERE checkintime>=DATE_TRUNC('DAY',checkintime)+interval '9 hours' +interval '30 minutes'

So by using this you can get expected result for all dates.

Upvotes: 0

user330315
user330315

Reputation:

Asssuming checkintime is properly declared as a time column, just add an interval:

select *
from attendancetable 
where checkintime = time '09:00:00' + interval '30 minutes';

But you probably want to use >= instead of = to also include employees that arrived e.g. 34 minutes late.

Upvotes: 1

Related Questions