• Registered: 2014-10-04
  • Posts: 154

Topic: Daysbettween in Database Calculated field!

Hi everyone,
I have a question.
I have a field in my database called date which is stored date string from date time picker.
I just want to calculate days between that date and today in database calculated fields.
how should I write my query?

many many thanks

  • Registered: 2015-02-24
  • Posts: 446

Re: Daysbettween in Database Calculated field!

Hello sonixax,


Assuming your dates are stored in the following format : yyyy-mm-dd you could try something like this :


SELECT JULIANDAY(end_date) - JULIANDAY(start_date) AS date_difference_in_days FROM table_name

If the date you want a difference with is the date of today, you could use :

SELECT JULIANDAY('now') - JULIANDAY(start_date) AS date_difference_in_days FROM table_name

And if you want to include the current day in the total, just add a +1 to your query

SELECT JULIANDAY('now') - JULIANDAY(start_date) + 1 AS date_difference_in_days FROM table_name

SIDE NOTE : you will get decimals to the calculation, you could round the result and get it without decimals with :

SELECT FLOOR(JULIANDAY('now') - JULIANDAY(test_date) + 1) AS days_inclusive FROM asset

FLOOR will only keep the non decimal part of your result while ROUND would behave differently if the decimal part is lower or greater that 0.5 ROUNDING up or down.


Hope it helped


Cheers


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-10-04
  • Posts: 154

Re: Daysbettween in Database Calculated field!

Yep It worked,
Many Many thanks

  • Registered: 2014-10-04
  • Posts: 154

Re: Daysbettween in Database Calculated field!

Hi,
Many thanks again for your help.
Just have another question.

this is the query which is works fine

(SELECT JULIANDAY('now') - JULIANDAY(date) AS date_difference_in_days FROM payments where payments.id_customers = customers.id ORDER BY payments.id DESC LIMIT 1)

I just want to change it a little bit.

If there is no date record in payments records then I want to take start_date field from bookings table instead.

is it possible?!

  • Registered: 2015-02-24
  • Posts: 446

Re: Daysbettween in Database Calculated field!

Hello sonixax,

I have setup a very simple database with 3 tables :

  • test_customers

    • cID

    • cName

  • test_reservations

    • rID

    • rDate

    • cID

  • test_payments

    • pID

    • pDate

    • rID

So the payment (which comes AFTER the reservation) is linked to the reservation on the reservation number (ID) and the reservation is linked to the customer on the customer number (ID).


This query

SELECT
     test_customers.cName,
     test_reservations.rDate,
     test_payments.pDate,
     julianday('now') - coalesce(julianday(test_payments.pDate),julianday(test_reservations.rDate)) AS pDelay
FROM
     test_customers
INNER JOIN test_reservations ON test_customers.cID = test_reservations.cID
LEFT JOIN test_payments ON test_reservations.rID = test_payments.rID

calculates the delay between now and payment OR now and reservation of no payment was found.
The COALESCE SQL instruction tells the SQL engine to take the first parameter by default, or the second if the first is NULL.


The LEFT JOIN to the test_payments is because INNER JOIN would exclude all missing records when LEFT JOIN includes the results even if not found.


The ouput looks like this, since there was no payment date for Lucien (but a reservation date)

https://i.postimg.cc/kXV0Xpdy/image.png


I hope this helped


Cheers


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor