sql: calculate number of hours per month

Question

i want to calculate the hours between dat_end and dat_beg group by id and month.

import pandas as pd
df = pd.DataFrame({'id':['x1', 'x1', 'x1', 'x2', 'x2', 'x2', 'x2']
           ,  'date_beg':['2021-01-01 00:00:00',
           '2021-02-03 00:00:00','2021-02-04 00:00:00','2021-02-05 00:00:00',
           '2021-02-06 00:00:00','2021-03-05 00:00:00','2021-04-08 00:00:00'],
              'date_end':['2021-01-02 00:00:00 ',
           '2021-02-03 12:00:00','2021-02-04 10:00:00','2021-02-05 10:00:00',
           '2021-02-06 10:00:00','2021-03-07 10:00:00','2021-05-08 00:00:00']}

expected output:

x1 01/2021  48
x1 02/2021  22
x2 02/2021    20
x2 03/2021     58
x2 04/2021 720
x2 05/2021 744

you should probbably check out this: https://stackoverflow.com/questions/37078370/pandas-function-equivalent-to-sqls-datediff Also you should keep in mind: here you wont get a working solution here, you have to bring up somethng by yourself. No spoonfeeding. — Kiwimanshare, Apr 22 '21 at 09:57
Why is the question tagged "sql" if the data is in a dataframe? — Gordon Linoff, Apr 22 '21 at 10:50

score 0 · Answer 1 · answered Apr 22 '21 at 09:48

0

Your title says number of days and in the question it is number of hours? Also what kind of sql? In tsql there is DATEDIFF() function:

SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
SELECT DATEDIFF(day, '2017/08/25', '2011/08/25') AS DateDiff;
SELECT DATEDIFF(month, '2017/08/25', '2011/08/25') AS DateDiff;
SELECT DATEDIFF(hour, '2017/08/25', '2011/08/25') AS DateDiff;

See: https://www.w3schools.com/sql/func_sqlserver_datediff.asp

answered Apr 22 '21 at 09:48

Kiwimanshare

130
9

what do you mean by **what kind of sql** please? also this function dont groupby month and id . Thank you – excellt Apr 22 '21 at 09:52
@excellt: the syntax in tsql and mysql for example is not the same, and there are function in tsql that are not available in mysql and the other way round, so pandas again is something else. However as it seems you work with panda. So maybe you should check out this: https://stackoverflow.com/questions/37078370/pandas-function-equivalent-to-sqls-datediff – Kiwimanshare Apr 22 '21 at 09:56

score 0 · Answer 2 · answered Apr 22 '21 at 10:21

You can do it like that

select extract(days from ('20210401 12:00:00'::timestamp - '20210401 00:00:00'::timestamp)) * 24 + extract(hours from ('20210401 12:00:00'::timestamp - '20210401 00:00:00'::timestamp))

Just create a function in postgresql fgethours(timestamp,timestamp) and it's done

sql: calculate number of hours per month

2 Answers2