I've haved use Postgresql for awhile now, but I am new to stored procedures. I have no idea how to implement this function in SQL. I want to create a SQL function that takes two arguments, a start time and an end time. Then, from those two times I want to calculate "night hours" (how many hours in the given range are between 11pm and 5am). If anyone has a better idea for how to implement this, then please tell me. I figure 4 things need to be checked for in this function:
1. Add hours if both strtime and endtime are earlier than 5am.
2. Subtract strtime from 5am if strtime is earlier but endtime is later than 5am
3. Subtract endtime from 11pm if strtime is earlier but endtime is later than 11pm
4. Add hours if both strtime and endtime are later than 11pm.
Of course, if strtime is earlier than 5am and endtime is later than 11pm, then it would need to do both steps 2 and 3. If possible, I don't want to write to any tables, but just modify a new value and return it. Is that possible? Thanks.