B4J Question SQLITE dates

[powerino]

Hello everyone, I would like to store dates in a sqlite table (in integer format) and then read the date from DATABASE and display it in my form (possibly also make SQL searches with BETWEEN). I can not find examples in the forum, can anyone tell me how to do it? thank you

 

[Erel]

What exactly is the question? Do you know how to insert the dates to the database?

 

[powerino]

Hi Erel, of course I know how to write the dates (in my case I set the field as an integer) and I do it as in the code below:

        pers.setName(txtNome.Text)
        pers.setLastName(txtCognome.Text)
        pers.setAge(txtEta.Text)
        Dim s As String
            
        s="UPDATE persona SET Attivo='" & 0 & "', DataModifica = '" & DateTime.Now & "' WHERE ID =" & correnteRS & "
        'Log(s)
        Main.sql.ExecNonQuery(s)

Then if I look at the table in the database, I see that in the Modified Date field there is a number and not the date, so I would like to understand how this "conversion" works and how to recover the date from this number, ok?

 

[LucaMs]

I see that in the Modified Date field there is a number and not the date, so I would like to understand how this "conversion" works

Did you tried what Log(DateTime.Now) shows? It is a Long.
https://www.b4x.com/guides/B4xSQLiteDatabase/?page=1
https://www.b4x.com/guides/B4xSQLiteDatabase/?page=15
https://www.sqlitetutorial.net/sqlite-date/

 

[powerino]

Si, è un lungo: 1582563697591

 

[LucaMs]

Si, è un lungo: 1582563697591

Mine was not a question (or, better, just the first part of my post).

 

[powerino]

thanks for your links, I will study them

 

[klaus]

You may have a look at the B4X SQLite Database booklet.

 

[yfleury]

You can take a look at https://www.sqlite.org/lang.html

 

[aeric]

s="UPDATE persona SET Attivo='" & 0 & "', DataModifica = '" & DateTime.Now & "' WHERE ID =" & correnteRS & "

First, this query string is incorrect. You should not surround Long value with single quotes.
Second, it is recommend to use Prepared Statement.

I recommend you check Erel’s SQL Tutorial

SQL tutorial

Update 2018: New video tutorial: The source code is available here: https://www.b4x.com/android/forum/threads/b4x-erel-teaches-programming-video-tutorials.88787/page-2#post-577932 Async methods: [B4X] SQL with Wait For The following tutorial is obsolete: This tutorial covers the SQL...

www.b4x.com

 

[powerino]

I solved it using TEXT. I prefer to see the date formatted as I want in the table (rather than Ticks), and I have seen that I can write, read and search. Thanks for your help

 

[Harris]

I solved it using TEXT. I prefer to see the date formatted as I want in the table (rather than Ticks), and I have seen that I can write, read and search. Thanks for your help

I solved it using TEXT...

IMHO, this is the wrong approach, but you are free to shoot yourself in the foot anytime you wish.

Erel and community members have gone to great lengths to ensure that date types (ticks as long) - works best under all situations - with minimal code (ie. DateUtils).

Eventually, as I did, you shall see....

 

[LucaMs]

He can always convert the date text to "ticks", when needed.

 

[Harris]

Another step... back and forth - forth and back....
Depending on stored format - this can be problematic - as I experienced... (ISO is best - yet you can do what you want - when you don't consider the consequences).

Ticks... or be damned... (yet this (ticks) can give Lyme disease)

 

[aeric]

The advantage of using text for date is readability.
Use the code to convert to ticks when during comparison.

Dim lngTicks As Long = DateTime.DateTimeParse(strDate, strTime)

 

[powerino]

I needed to solve the problem quickly. I read on the forum that it's not the ideal solution, and I believe it, but I didn't have time to understand how Ticks work. It may also be that I will not even use sqlite as a database, I will evaluate based on the project that I will have to implement.

 

[powerino]

I like to display the date in its correct format and not an "incomprehensible" number. Surely there will be a way to display it after transforming the Ticks (in the database table), but I still have to get there

 

[powerino]

I solved it using TEXT...

IMHO, this is the wrong approach, but you are free to shoot yourself in the foot anytime you wish.

Erel and community members have gone to great lengths to ensure that date types (ticks as long) - works best under all situations - with minimal code (ie. DateUtils).

Eventually, as I did, you shall see....

hello, thanks for the answer, if you have an example to show, the study, thanks

 

[aeric]

I would like to store dates in a sqlite table (in integer format) and then read the date from DATABASE and display it in my form (possibly also make SQL searches with BETWEEN)

tick = count of milliseconds (1/1000 s)
DateTime.Now = number of ticks since 1/1/1970 until current time

A good reason to use number to store date and time is we can compare number to another number. The question is how we compare the value of two strings?
Using DateTime.Now, we can compare this number with another value in database which also a number (without need to convert).

strQuery = "SELECT id FROM persona WHERE ExpiredDate < " & DateTime.Now

Second is we eliminate the problem of inconsistent date format like dd/MM/yyyy, yyyy-MM-dd, MM/dd/yyyy etc.

So, it is a personal choice to use TEXT or INTEGER to store the date. As for me, I store both.

 

[powerino]

[QUOTE = "aeric, post: 714575, membro: 74499"]
tick = conteggio dei millisecondi (1/1000 s)
DateTime.Now = numero di tick dall'1 / 1/1970 fino all'ora corrente

Un buon motivo per utilizzare il numero per memorizzare la data e l'ora è che possiamo confrontare il numero con un altro numero. La domanda è: come confrontiamo il valore di due stringhe?
Usando DateTime.Now, possiamo confrontare questo numero con un altro valore nel database che anche un numero (senza la necessità di convertire).
[CODE = sql] strQuery = "SELEZIONA ID DA persona DOVE ExpiredDate <" & DateTime.Now [/ CODE]

In secondo luogo, eliminiamo il problema di un formato data incoerente come gg / MM / aaaa, aaaa-MM-gg, MM / gg / aaaa ecc.

Quindi, è una scelta personale utilizzare TEXT o INTEGER per memorizzare la data. Per quanto mi riguarda, conservo entrambi.
[/CITAZIONE]

I use this query to search between 2 date, and it works good

s="Select * FROM Persona WHERE strftime('%Y-%m-%d', DataModifica) BETWEEN '" & data1 & "' AND '" & data2 & "';"