Android Question select date values stored as numbers from sqlite db

[Mario Krsnic]

Hi,
I store date values as long in a table of sqlite db.

Now I would like to select data with the certain date, picked with B4XDateTemplate. So I have a date in format "dd.MM.yyyy".



I try with this code, without success:

Try

    DateTime.DateFormat="dd.MM.yyyy"
        Dim strgDate As String
        strgDate=$"$Date{DateTemplate.Date}"$
        Dim Output As String
        
    Dim rs As ResultSet
    Private Query As String
    
        Query = "SELECT Data, date(data) as expr    from protocol where  Data='strgDate'"
        rs = Starter.SQL1.ExecQuery(Query)
        Output=rs.GetString("expr")

I do not get any output.
Please help me with some suggestion!
Mario

 

[Mahares]

I do not get any output.

If the date you get from B4XDateTemplate is a string like this: 09/07/2020, you need to parse it first before you can compare it to the SQLite date which is stored as long (ticks). You code should be something like this (Untested of course)

Dim rs As ResultSet
    Dim Query As String
    Dim Output As String
    Dim strDate As String  'from B4XDateDIalog
    Dim lngDate As Long =DateTime.DateParse(strDate)
    Query = "SELECT Data from protocol where  Data= ?"
    rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))
    Do While rs.NextRow
        Output=rs.GetString("Data")   'or maybe rs.getlong
    Loop

 

[Mario Krsnic]

If the date you get from B4XDateTemplate is a string like this: 09/07/2020, you need to parse it first before you can compare it to the SQLite date which is stored as long (ticks). You code should be something like this (Untested of course)

Dim rs As ResultSet
    Dim Query As String
    Dim Output As String
    Dim strDate As String  'from B4XDateDIalog
    Dim lngDate As Long =DateTime.DateParse(strDate)
    Query = "SELECT Data from protocol where  Data= ?"
    rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))
    Do While rs.NextRow
        Output=rs.GetString("Data")   'or maybe rs.getlong
    Loop

Thank you for showing me the direction. Unfortunately I get the error message in the line

rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))

java.lang.ClassCastException: java.lang.Object[] cannot be cast to java.lang.String[]

 

[Alex_197]

Thank you for showing me the direction. Unfortunately I get the error message in the line

rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))

java.lang.ClassCastException: java.lang.Object[] cannot be cast to java.lang.String[]

make sure that rs.GetString("Data") returns something before assign it's value to String Output

 

[Mario Krsnic]

No. The error is in the line:
rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))
My Code:

Sub Calculation
Try

    DateTime.DateFormat="dd.MM.yyyy"
        Dim strgDate As String
        
        strgDate=$"$Date{DateTemplate.Date}"$
        Dim lngDate As Long=DateTime.DateParse(strgDate)
        

    Dim rs As ResultSet
    Private Query As String
    
        Query = "SELECT Data,  Fin   from protocol where Data= ?"
        rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))
    
    
        rs.Close
    Catch LastException
    'MsgboxAsync ( LastException.Message,"Error"  )
    Log(LastException.Message)
End Try
End Sub

 

[DonManfred]

try

rs = Starter.SQL1.ExecQuery2(Query, Array As String(lngDate))

 

[Mahares]

The error is in the line:
rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))

I did have a chance to look at your project. It is hard to follow because a lot of it is in Spanish that I cannot make up. However:
If you change this line :

rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))

to this line, you will not get the error and it works:

rs = Starter.SQL1.ExecQuery2(Query, Array As String(lngDate))

But your problem comes up later. Read on:
You store the date in the SQLite database table called 'protocol' as Datetime(now) in ticks which has the date and time components combined. Then, you use B4XDateTemplate to compare the calculated ticks from that date to the one stored in the database which is ticks. Both are different from each other, so you will never match any records in the table. If you change the date you store in the database when you INSERT from:

Dim mojDatum As Long=DateTime.Now

to:

Dim mojDatum As Long=DateTime.DateParse(DateTime.Date(DateTime.Now))

The date will be saved as ticks, but will not have the time component. This way you will be able to compare the date ticks from the calendar template to the stored date ticks in the table. I am not sure though if you wanted to change the way you store your dates. That is up to you. Maybe something later in the app will be affected and prevents you from going that route..

 

[Mario Krsnic]

I did have a chance to look at your project. It is hard to follow because a lot of it is in Spanish that I cannot make up. However:
If you change this line :

rs = Starter.SQL1.ExecQuery2(Query, Array As Object(lngDate))

to this line, you will not get the error and it works:

rs = Starter.SQL1.ExecQuery2(Query, Array As String(lngDate))

But your problem comes up later. Read on:
You store the date in the SQLite database table called 'protocol' as Datetime(now) in ticks which has the date and time components combined. Then, you use B4XDateTemplate to compare the calculated ticks from that date to the one stored in the database which is ticks. Both are different from each other, so you will never match any records in the table. If you change the date you store in the database when you INSERT from:

Dim mojDatum As Long=DateTime.Now

to:

Dim mojDatum As Long=DateTime.DateParse(DateTime.Date(DateTime.Now))

The date will be saved as ticks, but will not have the time component. This way you will be able to compare the date ticks from the calendar template to the stored date ticks in the table. I am not sure though if you wanted to change the way you store your dates. That is up to you. Maybe something later in the app will be affected and prevents you from going that route..

Thank you very much for your time and solution! Naturally I will change the way I store my dates, if it helps to be able to sort data. I will be obliged to add a new column for time as well, but it does not make any problem! Thanks again!

 

[Mahares]

A very good way to store dates is yyyy-MM-dd. This way you can easily sort without having to worry about ticks, although some prefer ticks

 

[Mario Krsnic]

You mean, to store dates as strings, not as numbers?
But in the columns Start and End where I store times I think that I must have ticks to calculate elapsed time from the beginning to the end of activity.
It should be so:
Activity A: start: 10:00 End: 12:00
then later again:
Activity A: start: 16:00 End: 19:00
I need to calculate the sum of all times of an activity for a certain day, in this case 5 hours.
Or do you think I should store times as well as strings?

 

[Alex_197]

A very good way to store dates is yyyy-MM-dd. This way you can easily sort without having to worry about ticks, although some prefer ticks

Not a good idea at all. If you want to get latest date it will not work because it will be sorted as string but as dates.

 

[Mahares]

Activity A: start: 16:00 End: 19:00

If you are going to involve the time in your calculations and still be able to sort, then one way I would do it is store the date and time together in this date format:
yyyy-MM-dd HH:mm:ss This way you can sort the date/time and be able to convert to ticks and do all kind of math. For example, if your start time is 2020-09-07 19:23:15 and your end time 2020-09-07 21:08:45, you can easily calculate the time between. It is a matter of preference whether you want to store the date as ticks or a date format like this: yyyy-MM-dd HH:mm:ss. This topic was brought up before several times in the forum.
Here are 2 identical dates one as a date time. 2020-09-07 17:10:13 the other as ticks: 1599513013431. If you look at them , which one is easier to read and looks like a date when you look at your table short of doing all kind of conversions. To me, it is a no brainer: 2020-09-07 17:10:13

Not a good idea at all. If you want to get latest date it will not work because it will be sorted as string but as dates.

For a date format: yyyy-MM-dd , you can always sort properly (ASC or DESC) whether it is a date or string. For instance, 2020-09-27 is always lower than 2020-11- 06. Give me one of your examples that you are thinking of, where it will not sort properly.

 

[Mario Krsnic]

If you are going to involve the time in your calculations and still be able to sort, then one way I would do it is store the date and time together in this date format:
yyyy-MM-dd HH:mm:ss This way you can sort the date/time and be able to convert to ticks and do all kind of math. For example, if your start time is 2020-09-07 19:23:15 and your end time 2020-09-07 21:08:45, you can easily calculate the time between. It is a matter of preference whether you want to store the date as ticks or a date format like this: yyyy-MM-dd HH:mm:ss. This topic was brought up before several times in the forum.
Here are 2 identical dates one as a date time. 2020-09-07 17:10:13 the other as ticks: 1599513013431. If you look at them , which one is easier to read and looks like a date when you look at your table short of doing all kind of conversions. To me, it is a no brainer: 2020-09-07 17:10:13


For a date format: yyyy-MM-dd , you can always sort properly (ASC or DESC) whether it is a date or string. For instance, 2020-09-27 is always lower than 2020-11- 06. Give me one of your examples that you are thinking of, where it will not sort properly.

Thank you Mahares. It is good to know about these two possibilities. For now I implemented your idea to store dates as string in format "dd.MM.yyyy", so I can easy sort them by a certain date. I store the times (start and end of activities) as ticks, so I can calculate easily duration. Yes, I am aware of the problem wenn an activity starts in one day and ends in one another, but this is not so important in the project.
Thanks again!

 

[Mahares]

tore dates as string in format "dd.MM.yyyy", so I can easy sort them by a certain date.

No. I did not say to use dd.MM.yyyy. I said one of the options is to use: yyyy-MM-dd (or yyyy.MM.dd ) so you can sort properly. If you sort in dd-MM-yyyy you will have problems sorting. For instance, 30-01-2020 will be higher than 15-03-2020. You do not want that. Still my preferred way is: yyyy-MM-dd HH:mm:ss.

 

[Mario Krsnic]

But I need the format "dd.MM.yyyy" in the B4XTable. If I store dates in format yyyy-MM-dd HH:mm:ss how can I show them in the format dd.MM.yyyy in the table? The format yyy-MM-dd HH:mm:ss would be very unusual for the app users.

 

[Mahares]

But I need the format "dd.MM.yyyy" in the B4XTable. If I store dates in format yyyy-MM-dd HH:mm:ss how can I show them in the format dd.MM.yyyy in the table? The format yyy-MM-dd HH:mm:ss would be very unusual for the app users.

Hi Mario:
You can add an extra column formatted as text for the date to show as dd.MM.yyyy using the Substr function of SQLite. The column will be next to the date column with format: yyyy-MM-dd HH:mm:ss so, you have both on the same B4XTable or you can hide one if you like. I can make you a little project to give you an idea if you like. But in the end, you are the one to decide what you like to pursue.

 

[Mario Krsnic]

Hi Mario:
You can add an extra column formatted as text for the date to show as dd.MM.yyyy using the Substr function of SQLite. The column will be next to the date column with format: yyyy-MM-dd HH:mm:ss so, you have both on the same B4XTable or you can hide one if you like. I can make you a little project to give you an idea if you like. But in the end, you are the one to decide what you like to pursue.

Thank you, Mahares, you have helped me enough. I will think about your idea, you have explained it clearly!

 

[Mahares]

you have explained it clearly!

Let me clarify something: As long as you are only showing your data in a B4XTable with a date column and clicking the headers to sort, you are perfectly ok with using a format like this: dd.MM.yyyy. There is nothing wrong using your format that the users are accustomed to. You could have issues if you try to sort later with that column somewhere else other than B4XTable. Good luck.