Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Sqlite3: How to calculate the difference of two dates?

SQLite sqlite

  • Please log in to reply
8 replies to this topic

#1 Lance

Lance

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 270 posts

Posted 06 March 2009 - 08:20 AM

For example, if I have a modified_date field in table T, how do I select those with modified_date within most recent 5 days?

Is there other utility function to convert a datetime to a float number?

Thank you for your help!
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 06 March 2009 - 08:28 AM

For most SQL databases, you would do something like:
SELECT * FROM T WHERE MODIFIED_DATE >= [five days ago]
Usually, I do conversions within the language I'm using to access the database.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Lance

Lance

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 270 posts

Posted 06 March 2009 - 09:19 AM

Thanks Panther.

That's not exactly what I want. The thing is the date field is actually treated as a string. so '2009-3-15' will be greater than '2009-03-15', etc. I was wondering if there was a way to treated it as TIMESTAMP/DATETIME.

Is is because Sqlite3 doesn't have native datetime datatype support?

And yes, your proposal should work if I make sure datetime values always be formatted as yyyy-mm-dd hh:MM:ss before insert into a table.
  • 0

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 06 March 2009 - 10:29 AM

I would approach it differently (having just done some reading). I would store DATETIMEs as floating point values, where the integer part is the number of days after a certain date, and the decimal part is the fraction of the day. Since many languages store datetime values with this approach anyway, I think it may be your best approach.
  • 1

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 Lance

Lance

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 270 posts

Posted 06 March 2009 - 11:08 AM

Great idea!!

Should satisfy my needs. Thanks a lot!
  • 0

#6 Lance

Lance

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 270 posts

Posted 06 March 2009 - 11:09 AM

I would have Rep+'d /Credit+'d you but I don't know how to :(
  • 0

#7 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 06 March 2009 - 11:32 AM

Just use the little scale icon next to the #4(permalink)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#8 Lance

Lance

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 270 posts

Posted 06 March 2009 - 12:10 PM

Done.
  • 0

#9 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 06 March 2009 - 02:00 PM

or there is the unix method of storing time, an long integer with seconds since a certain time (1970-01-01 00:00:00). most programming languages has built-in support for converting this.
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download