DateTime Ticks and SQLite... not easy, need help :)

[RiverRaid]

Hi!

I'm fairly new to b4a and completele new to SQLLite...
Now I store a date and the time as one Field (STRING) as Ticks in an SQLite - DB. Works quite good

But now I want to filter by Days, so a simple

    For o = 0 To AnzahlEintraege
      Dim tmpDay As Long
      tmpDay = DateTime.Add (DateTime.Now,0,0,o)
      tmpDay = DateTime.
      Dim Table As List
      Table = DBUtils.ExecuteMemoryTable(Main.SQLBD, "SELECT * FROM Blutdruck WHERE Datum = ? ORDER BY Datum ASC", Array As String(tmpDay), 0)
    Next

wont work, because the values are, due do the stored time, not the same :-/

Do you have an Idea how to get this done??

THANKS!!

 

[lagore]

Hi,
I normally store my dates/times as a integer field in sqlite, using B4A long/1000 as sqlite stores the date/time in seconds. Then it is a simple search in the data base constructing your query something like

"SELECT YourField FROM yourtable WHERE startdate > YourStartDate and enddate < YourEndDate ")

to save a lot of time and effort I use Sqlite manager in firefox to run my queries on my pc to get the syntax correct before running it on android

 

[tremara1]

Used largeint

I have used largeint field format in the db to store date and time ticks value and have been able to use date math and if statements to make selections. Seems to work ok.

 

[RiverRaid]

@lagore
WOW, thanks! It works :sign0098:

@tremara1: Thank you for the reply