Calc Date difference

Getting Started
1

I’m trying to take a a date stored in a sqlite database (yyyymmdd) and figure out how long it’s been since that date (month days and years) , display it then sort by it… I did it in php and now I’m trying to teach myself with xojo… Anyone able to help? It would be greatly appreciated :slight_smile:

2

Can you post the code that you have? Do you want to do the date diff with SQL or with Xojo code?

3

Parse the date and then calculate the difference with “Date.TotalSeconds”.

Dim today As new Date
Dim differenceSeconds As Integer

differenceSeconds = today.TotalSeconds - yourParsedDate.TotalSeconds

And from Seconds to Minutes, Hours, Days… should be easy.

5

I had been looking on how to do it with Xojo, I have been racking my brain all day never realizing I could do it with SQL!!! Lol thank you so much for your help!
I would actually love to learn how to do it with so xojo but this will do!
YA’LL ARE THE BEST :slight_smile:

6

Actually… Daniel… Each item in my database has a date in which it was acquired… how would I parse that info into the yourParsedDate.TotalSeconds???

Sorry for my excitement there… Didn’t stop and think lol I was just so dang tired of going through the forums and all the petty arguments that seem to crop up lol…

7

I don’t have any code as of yet… It’s what I’m trying to understand and put together with my sql query and the actual presentation of the data onto a Listbox :slight_smile:

8

[quote=175400:@Adam Contreras]Actually… Daniel… Each item in my database has a date in which it was acquired… how would I parse that info into the yourParsedDate.TotalSeconds???

Sorry for my excitement there… Didn’t stop and think lol I was just so dang tired of going through the forums and all the petty arguments that seem to crop up lol…[/quote]

You said you are using a sqlite database. So there’s no Date datatype. Dates in Sqlite should be saved in the format YYYY-MM-DD. Then Xojo automagically parses the date and you can retrieve it with the function ‘DateValue’ of you RecordSet.

You could write your own Parser using the function like:

Dim d As new Date
d.Year = Val(yourDateString.Mid(1,4))
d.Month = Val( yourDateString.Mid(5,2))
d.Day = Val( yourDateString.Mid(7,2))

This code is only a example. There is no error checking or something else for simplicity.

I found a another forum topic with a good Parse/Validate post: https://forum.xojo.com/3366-parsedate-replacement/0

9

So if you are a PHP pro it would be easy to port such a function.

10

Jajajaja… NOT a PHP Pro… Not even close :slight_smile: I’ve been teaching myself by doing projects here and there… I’ve learnt the most with hands on determination lol :wink:

11

If you want to do it in the SQL, then see the SQLite Date functions

[quote]Compute the number of days since the signing of the US Declaration of Independence.

SELECT julianday(‘now’) - julianday(‘1776-07-04’);
[/quote]

12

I am looking for this answer too, I learned from Daniel’s post and wrote this code. It looks like it works.

Dim age as Integer age = (today.TotalSeconds - birthday.TotalSeconds)/60/60/24/365 Label1.Text = str(age)+" Years Old"

but then I find something wrong, not every year is 365 days, every 4 years there is a Feb/29 we have to consider. But I don’t know how many Feb/29 between “today” and “birthday”, does anyone know how to solve this problem?

13

I give it a try to solve this problem, don’t know if this is correct, but this code says I am 40 years old, must be something wrong, I should not be that old.

[code] DIM today As New Date
DIM age As Integer
DIM i As Integer
DIM leapYear As Integer = 0

FOR i=pDate.Year TO today.Year
IF (i MOD 4) = 0 THEN
leapYear = leapYear + 1
END IF
NEXT
age = (today.TotalSeconds - pDate.TotalSeconds)/60/60/24/(365+leapYear)
Label1.Text = str(age)+" Years Old"[/code]

14

Second solution, I gave up calculating leapYear, only calculate how many birthday passed since the date of birth.

  DIM today As New Date
  DIM age As Integer
  DIM i As Integer
  DIM birthday As New Date

    birthday = pDate
    FOR i = pDate.Year+1 TO today.Year
      birthday.Year = i
      IF birthday<today THEN
        age = age + 1
      END IF
    NEXT
    Label1.Text = str(age)+" Years Old"
15

Neil deGrasse Tyson was just talking about this on StarTalk. Leap year isn’t every 4 years. Every 100 it’s skipped, year 2000 didn’t leap, but every 400 it’s put back in. Or something like that. Anyways, instead of learning to calculate the calendar let the Date class handle it.

Measure the difference in years then subtract 1 if today is earlier in the year than birthday.

[code]dim age As integer = today.Year - birthdate.Year

if today.DayOfYear < birthdate.DayOfYear then age = age - 1

Label1.Text = Str(age) + " years old"[/code]

Untested and maybe my logic is off. Well my logic is off because I don’t know it handles birthdays or todays near or on Feb 29th. hmmm…

16

“I am the very model of a modern major general” – http://blogs.perl.org/users/wolfgang_kinkeldei/2012/02/date-arithmetic-can-be-dangerous.html

17

@Will Shank: WOW, 3 line of code solved it.