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:

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

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

Short URL for this post: http://wp.me/p4nxik-Ur
Roland Krüger

About Roland Krüger

Software Engineer at Orientation in Objects GmbH. Find me on Google+, follow me on Twitter.
This entry was posted in Did you know? and tagged , , . Bookmark the permalink.

Leave a Reply