Firebird – Calculate Time Interval Between Two Dates

datefirebird

I try to select the period of employment for the members in a Company.

Example:
Date of join: 01.01.2001
Today: 27.02.2014
Now, I want this:
13 years, 1 month and 27 days

I have a Statement which works on MSSQL, but I cant translate it to firebird-SQL.

Could anybody help me?

Thx

Erik

Best Answer

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance! Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that just will not lose).

Thanks and hello again! --Rob

I never had a plan-- Anonyman

Related Question