How do you work with date field in sqlite?

[Horst_Schulze]

Hi,
I have a database on my webserver and in my app. Now I want to know if both are identic. (reason: Others may have changed data on the Webserver)
Both have a changed (timestamp) field.
I made a select sum(changed) query, but sqlite gives only the sum of the years.

My webdb ($sql="SELECT sum(changed) FROM td_caisse_$kasse"; ) gives a great number .

I tried several expressions, but that does not work.

(My programm is:
look if data are changed; #Thats the problem.
if yes load data.
)

[TIMAI2]

How do you work with date field in sqlite? (Topic Title Question)

https://www.sqlite.org/datatype3.html#:~:text=SQLite%20does%20not%20have%20a%20storage%20class%20set%20aside%20for,%3ASS.SSS"

https://www.sqlite.org/lang_datefunc.html

[Horst_Schulze]

Thanks, in fact I found these pages, but it is not really helpfull. Date can be stored as text or numbers, but how? I have: create table ...(..., changed date,...
So that should be numeric but it isn't.
And the exemples do work but not with a field of the Database.
I did not found en exemple for a date field.
I tried: select ..., datetime(changed, 'unixepoch')...
So how to make the date field numeric?
by the way, is there another way to compare a webDB with the appDB?

[TIMAI2]

OK Horst

It is not really very clear from what you have posted already what you are doing and where the problem is. I am assuming you are accessing a mySQL database online via php calls using the web component?

And you are using TaifunSqlite to access the local sqlite database.

Provide clear examples of the data, the call/request you are making to each database, and the data response you are getting. it would also help to show clearly how you are setting the data for each of your databases.

You also may wish to simplify your queries to help identify the problem.

As an aside, you might want to consider running sqlite on your online server, which you can access via php calls, then you are working with the same underlying database. See HERE for more details.

[ABG]

Assuming you don't allow deletions from the database, you could detect the need to re-sync by asking for max(changed), not sum(changed.)

When setting up the tables, make sure to ask for one of the internal formats based on a number (milliseconds from 1970 in AI2 Clock1.DateTime, or days since 1899 in Excel-like spreadsheets) and specify what external format should be used with that datetime.

That enables datetime math in the database, if you have a need for that.

Consider yyyy/MM/DD HHMM as an external datetime format, to make it sortable with text comparison. That format aligns time with text.

[Horst_Schulze]

hi all and thanks,
My whole app is too great an I have only a simple problem in it.

I have two databases:
WEbDB, working with php
and
AppDB on the mobile with sqlite
The webDB has some more fields for the accoutant

some php
.... /* I got these data and I put them into ma DB. that works
only the format of the field changed is different

*/
case "datenholen":
$datkeys="id,datum,libelle,recette,depense,imputation,status,checkID,upd,changed ";
$sql="select $datkeys from td_caisse_" .$kasse . " where id >'$query'"." order by id " ;
echo "$webvar#$kasse#$nutzer#".arraytocsv(holsqlarray($sql,$conn));

....
switch ($webvar){

  case "datenkonsistenz": #webvar to point to this case
  $sql="SELECT sum(`changed`) FROM td_caisse_$kasse"; 

#kasse sent by runquery, nutzer for access control
#$sql="SELECT sum(changed) FROM td_caisse_$kasse"; # or what
$result=fcgibfeld(fc_sqlSelect($sql,$conn)); # formatting the outpout data for display on the mobile
# problem: this result is different from the result in the app
echo($webvar."#$kasse#$nutzer#$result#$query#xx");# sending to App
exit;
break;

#....

datenholen works and the data are in the AppDB. Now I want to check if the data are identic.
Because:

1. User Albert works with the App not having Internet,
   User Bert online with the Website,
   User comptable adds some remarques or explications to older DS.
2. Now Albert goe online gets the new Datasets - works
3. Then he has to check, if older sets are changed, If yes they will be corrected
4. Then he sends his own Dataset to the WebDB and getting the changed Data.
   maybe there is a better solution.

So my question is, that I do not know how to set the date format in sqLite.

you see: create table .. changed date,... or changed integer. I read the documentation, but I didn't found how the date format can be set to a number. I learned that maybe the date format for a field does not exist, but is stored as text. That was the result of all tries.

listing the table select changed gives '2023-03-05 16:23:56' or similar
select sum(changed) gives the sum of the years. so the field is text!
works fine, but
how to make/address/call or whatever with the changed-field?

@ABG

Assuming you don't allow deletions from the database, you could detect the need to re-sync by asking for max(changed), not sum(changed.)
not possible

When setting up the tables, make sure to ask for one of the internal formats based on a number (milliseconds from 1970 in AI2 Clock1.DateTime, or days since 1899 in Excel-like spreadsheets) and specify what external format should be used with that datetime.
Thats my question. I didnt found help in the documentation
That enables datetime math in the database, if you have a need for that.

Consider yyyy/MM/DD HHMM as an external datetime format, to make it sortable with text comparison. That format aligns time with text.
I use yyyy-MM-DD HH:mm:ss

[TIMAI2]

Use milliseconds and convert them for display in the app and on the website

[Horst_Schulze]

Thanks. I did this
Transfer fied changed from webDB: select ...(convert(changed,unsigned))
now it is stored as integer and
select ... sum(changed) - works in appDB gives same number as
select ... sum(convert(changed,unsigned) - in webDB.
I tested it only with one dataset. Let's hope that it works also on other