Calculate Time Spans by Quarters of an Hour With SQL

Suppose you have a database table containing two fields ‘start’ and ‘end’ of type timestamp denoting the starting time and ending time, respectively, of some time span. You can calculate the positive time span between these two points in time rounded to the nearest quarter of an hour with the following SQL snippet:

 ABS(EXTRACT (HOUR FROM table.end - table.start) +
 (ROUND((EXTRACT (MINUTE FROM table.end - table.start))
 / 15) * 0.25))

For example, this yields for the time span 10:30 – 12:00 a duration of 1.5 hours.

Short URL for this post:

About Roland Krüger

Software Engineer at Trivadis / Orientation in Objects GmbH. Follow me on Twitter.
This entry was posted in Did you know? and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *