I have database table and execute the below query to achieve certain result.
select a.c_bpartner_id as emp_id,a.mindatetime::date date1,a.mindatetime,a.maxdatetime, a.maxdatetime-a.mindatetime as w_hour
from c_pay_daily_attend a
where a.C_BPartner_ID=1008337
and a.mindatetime::date between '2019-12-01' and '2019-12-31'
Above my query i get below result.
| emp_id | date1 | mindatetime | maxdatetime | w_hour |
|---|---|---|---|---|
| 1008337 | 2019-12-01 | 2019-12-01 07:14:41 | 2019-12-01 14:21:36 | 07:06:55 |
| 1008337 | 2019-12-03 | 2019-12-03 14:04:07 | 2019-12-03 22:09:31 | 08:05:24 |
| 1008337 | 2019-12-04 | 2019-12-04 07:11:33 | 2019-12-04 22:03:42 | 14:52:09 |
| 1008337 | 2019-12-06 | 2019-12-06 07:00:18 | 2019-12-06 22:07:18 | 15:07:00 |
| 1008337 | 2019-12-05 | 2019-12-05 07:00:47 | 2019-12-05 22:04:18 | 15:03:31 |
| 1008337 | 2019-12-08 | 2019-12-08 07:19:59 | 2019-12-08 14:14:16 | 06:54:17 |
| 1008337 | 2019-12-13 | 2019-12-13 07:00:49 | 2019-12-13 22:07:32 | 15:06:43 |
| 1008337 | 2019-12-14 | 2019-12-14 07:03:38 | 2019-12-14 22:07:16 | 15:03:38 |
| 1008337 | 2019-12-15 | 2019-12-15 07:19:34 | 2019-12-15 14:15:07 | 06:55:33 |
| 1008337 | 2019-12-17 | 2019-12-17 14:11:59 | 2019-12-17 22:00:15 | 07:48:16 |
| 1008337 | 2019-12-18 | 2019-12-18 07:20:26 | 2019-12-18 22:12:56 | 14:52:30 |
| 1008337 | 2019-12-19 | 2019-12-19 07:04:09 | 2019-12-19 22:08:13 | 15:04:04 |
| 1008337 | 2019-12-20 | 2019-12-20 07:01:48 | 2019-12-20 22:04:43 | 15:02:55 |
| 1008337 | 2019-12-21 | 2019-12-21 07:01:43 | 2019-12-21 22:00:57 | 14:59:14 |
| 1008337 | 2019-12-22 | 2019-12-22 07:14:50 | 2019-12-22 14:11:41 | 06:56:51 |
| 1008337 | 2019-12-24 | 2019-12-24 14:11:21 | 2019-12-24 22:11:33 | 08:00:12 |
| 1008337 | 2019-12-25 | 2019-12-25 07:17:05 | 2019-12-25 22:04:55 | 14:47:50 |
| 1008337 | 2019-12-26 | 2019-12-26 07:00:29 | 2019-12-26 22:08:52 | 15:08:23 |
| 1008337 | 2019-12-27 | 2019-12-27 07:01:25 | 2019-12-27 22:06:18 | 15:04:53 |
| 1008337 | 2019-12-28 | 2019-12-28 07:00:24 | 2019-12-28 22:00:40 | 15:00:16 |
| 1008337 | 2019-12-29 | 2019-12-29 07:18:05 | 2019-12-29 14:14:25 | 06:56:20 |
| 1008337 | 2019-12-31 | 2019-12-31 14:14:43 | 2019-12-31 22:04:14 | 07:49:31 |
Now i need a query to get below result. where a.mindatetime::date between '2019-12-01' and '2019-12-31' condition may vary. like a.mindatetime::date between '2020-01-01' and '2020-01-31 or others.
| emp_id | 2019-12-01 | 2019-12-02 | 2019-12-03 | 2019-12-04 | 2019-12-05 | 2019-12-06 | 2019-12-07 | 2019-12-08 | 2019-12-09 | 2019-12-10 | 2019-12-11 | 2019-12-12 | 2019-12-13 | 2019-12-14 | 2019-12-15 | 2019-12-16 | 2019-12-17 | 2019-12-18 | 2019-12-19 | 2019-12-20 | 2019-12-21 | 2019-12-22 | 2019-12-23 | 2019-12-24 | 2019-12-25 | 2019-12-26 | 2019-12-27 | 2019-12-28 | 2019-12-29 | 2019-12-30 | 2019-12-31 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1008337 | 07:06:55 | null | 08:05:24 | 02:52:09 | 03:03:31 | 03:07:00 | null | 06:54:17 | null | null | null | null | 03:06:43 | 03:03:38 | 06:55:33 | null | 07:48:16 | 02:52:30 | 03:04:04 | 03:02:55 | 02:59:14 | 06:56:51 | null | 08:00:12 | 02:47:50 | 03:08:23 | 03:04:53 | 03:00:16 | 06:56:20 | null | 07:49:31 |

a.mindatetime::date between '2019-12-01' and '2019-12-31'condition may vary. likea.mindatetime::date between '2020-01-01' and '2020-01-31