Skip to content

Tilda Database functions: Duration

Jump to bottom
Laurent Hasson edited this page Sep 17, 2019 · 2 revisions

<-- Common Helper Database Functions

Source code in PostgreSQL.helpers-start.sql

Duration Helpers

A set of database helper functions to easily compute various durations between 2 date/time points.

Tilda.age

This function takes 2 timestamps with time zone parameters for a start and end and returns a double precision number representing the number of years, including fraction. For example, an 18-month old infant will have an age of 1.5y.

select Tilda.age('2017-01-10', '2019-01-09'); -- 1.99885844748858
select Tilda.age('2017-01-10', '2019-01-10'); -- 2
select Tilda.age('2017-01-10', '2019-01-11'); -- 2.0027397260274

select Tilda.age('2017-01-10 14:20:10', '2019-01-10 14:20:09'); -- 1.99885844
select Tilda.age('2017-01-10 14:20:10', '2019-01-10 14:20:10'); -- 2
select Tilda.age('2017-01-10 14:20:10', '2019-01-10 14:20:11'); -- 2
...
select Tilda.age('2017-01-10 14:20:10', '2019-01-11 14:20:09'); -- 2
select Tilda.age('2017-01-10 14:20:10', '2019-01-11 14:20:10'); -- 2.00273972
select Tilda.age('2017-01-10 14:20:10', '2019-01-11 14:20:11'); -- 2.00273972

🎈 NOTE: As demonstrated above, the resolution of date/time comparisons is daily.

🎈 NOTE: The function works with 'timestamp with time zone' types, so beware of timezone issues when dealing with fractional years. Comparing a timestamp in the EST timezone between February 15th and May15th for the same time will give exactly .25 (a quarter) vs what some may expect, i.e., one extra hour due to daylight savings time forward change.

select Tilda.age('2019-02-15 14:20:10', '2019-05-15 14:20:11'); -- 0.25

Tilda.ageBetween

This function works like age but allows to compare an age to a range. This avoids maybe calculating an age twice. It returns a boolean if the age falls within the range.

select Tilda.ageBetween('2019-02-15 14:20:10', '2019-05-15 14:20:11', 0, 0.25); -- false
select Tilda.ageBetween('2019-02-15 14:20:10', '2019-05-15 14:20:11', 0, 1); -- true

🎈 NOTE: Even though the function is named "between", it works with an exclusive range, i.e., x <= age < y, and not like the SQL 'BETWEEN' semantics which works with an inclusive range.

Tilda.daysBetween

This function returns as an integer the number of days between 2 timestamps with time zone. It has a variant to allow for specifying whether the midnight rule should be applied or not (as a third boolean parameter), which is useful in industries such as HealthCare for example.

-- midnight rule is off by default
select Tilda.daysBetween('2019-03-09 14:20:10', '2019-03-10 14:20:11'); -- 2

-- midnight rule: must cross midnight to count as a day
select Tilda.daysBetween('2019-03-09 14:20:10', '2019-03-10 14:20:11', true); -- 1
select Tilda.daysBetween('2019-03-09 14:20:10', '2019-03-10 14:20:11', false); -- 2

-- midnight rule: fine with Daylight Savings time change
select Tilda.daysBetween('2019-03-09 14:20:10', '2019-03-10 08:20:11', true); -- 1
select Tilda.daysBetween('2019-03-09 14:20:10', '2019-03-10 08:20:11', false); -- 2

-- midnight rule: A span of 23h 59mn and 59s, if it doesn't cross midnight, counts as
-- zero days whereas a span of 1s counts at 1 day if it does cross midnight.
select Tilda.daysBetween('2019-05-01 00:00:00', '2019-05-01 23:59:59', true); -- 0
select Tilda.daysBetween('2019-05-01 23:59:59', '2019-05-02 00:00:00', true); -- 1

🎈 NOTE: Note that the function returns the number of days in terms of calendar days, not 24h periods.

🎈 NOTE: Note that the midnight rule states that midnight must be crossed in order to count for a day. So the difference between 2019-05-01 00:00:00 and 2019-05-01 23:59:59 is 0 days, where as the difference between 2019-05-01 23:59:59 and 2019-05-02 00:00:00 will be 1 day.

Tilda.monthsBetween

Returns the number of months (with fraction) between the 2 timestamp with time zone.

-- 1 day resolution
select Tilda.monthsBetween('2019-05-01 00:00:00', '2019-05-01 23:59:59'); -- 0
select Tilda.monthsBetween('2019-05-01 00:00:00', '2019-05-02 00:00:00'); -- 0.033333333
select Tilda.monthsBetween('2019-05-01 00:00:00', '2019-05-02 23:59:59'); -- 0.033333333
select Tilda.monthsBetween('2019-05-01 00:00:00', '2019-05-03 00:00:00'); -- 0.066666666

-- 1 day = 24h period (i.e., not tied to the midnight rule)
select Tilda.monthsBetween('2019-05-01 23:59:59', '2019-05-02 00:00:00'); -- 0

-- some more examples
select Tilda.monthsBetween('2019-05-01 14:30:00', '2019-05-10 14:30:00'); -- 0.3
select Tilda.monthsBetween('2019-05-01 14:30:00', '2019-05-20 14:30:00'); -- 0.63333333
select Tilda.monthsBetween('2019-05-01 14:30:00', '2019-05-30 14:30:00'); -- 0.96666666
select Tilda.monthsBetween('2019-05-01 14:30:00', '2019-06-01 14:30:00'); -- 1

Tilda.quartersBetween

Returns the number of quarters (with fraction) between the 2 timestamp with time zone.

-- 1 day resolution
select Tilda.quartersBetween('2019-05-01 00:00:00', '2019-05-01 23:59:59'); -- 0
select Tilda.quartersBetween('2019-05-01 00:00:00', '2019-05-02 00:00:00'); -- 0.01098901
select Tilda.quartersBetween('2019-05-01 00:00:00', '2019-05-02 23:59:59'); -- 0.01098901
select Tilda.quartersBetween('2019-05-01 00:00:00', '2019-05-03 00:00:00'); -- 0.02197802

-- 1 day = 24h period (i.e., not tied to the midnight rule)
select Tilda.quartersBetween('2019-05-01 23:59:59', '2019-05-02 00:00:00'); -- 0

-- some more examples
select Tilda.quartersBetween('2019-05-01 14:30:00', '2019-05-30 14:30:00'); -- 0.31868131
select Tilda.quartersBetween('2019-05-01 14:30:00', '2019-06-30 14:30:00'); -- 0.65201465
select Tilda.quartersBetween('2019-05-01 14:30:00', '2019-07-30 14:30:00'); -- 0.98534798
select Tilda.quartersBetween('2019-05-01 14:30:00', '2019-08-01 14:30:00'); -- 1
select Tilda.quartersBetween('2019-05-01 14:30:00', '2019-08-30 14:30:00'); -- 1.31868131

Tilda.yearsBetween

Returns the number of years (with fraction) between the 2 timestamp with time zone.

-- 1 day resolution
select Tilda.yearsBetween('2019-05-01 00:00:00', '2019-05-01 23:59:59'); -- 0
select Tilda.yearsBetween('2019-05-01 00:00:00', '2019-05-02 00:00:00'); -- 0.0027397260
select Tilda.yearsBetween('2019-05-01 00:00:00', '2019-05-02 23:59:59'); -- 0.0027397260
select Tilda.yearsBetween('2019-05-01 00:00:00', '2019-05-03 00:00:00'); -- 0.0054794520

-- 1 day = 24h period (i.e., not tied to the midnight rule)
select Tilda.yearsBetween('2019-05-01 23:59:59', '2019-05-02 00:00:00'); -- 0

-- some more examples
select Tilda.yearsBetween('2019-01-01 14:30:00', '2019-04-01 14:30:00'); -- 0.25
select Tilda.yearsBetween('2019-01-01 14:30:00', '2019-07-01 14:30:00'); -- 0.5
select Tilda.yearsBetween('2019-01-01 14:30:00', '2019-10-01 14:30:00'); -- 0.75
select Tilda.yearsBetween('2019-01-01 14:30:00', '2019-12-31 14:30:00'); -- 0.99885844
select Tilda.yearsBetween('2019-01-01 14:30:00', '2020-01-01 14:30:00'); -- 1

-- Tilda.yearsBetween is the same as Tilda.age
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-10 14:20:09'); -- 1.9988584
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-10 14:20:09'); -- 1.9988584
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-10 14:20:10'); -- 2
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-10 14:20:10'); -- 2
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-10 14:20:11'); -- 2
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-10 14:20:11'); -- 2
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-11 14:20:09'); -- 2
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-11 14:20:09'); -- 2
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-11 14:20:10'); -- 2.002739
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-11 14:20:10'); -- 2.002739
select Tilda.yearsBetween('2017-01-10 14:20:10', '2019-01-11 14:20:11'); -- 2.002739
select Tilda.age         ('2017-01-10 14:20:10', '2019-01-11 14:20:11'); -- 2.002739

🎈 NOTE: Note that this function is the same as Tilda.age in every way except for its name.

Clone this wiki locally