Sqlite and clock storage options

[gipsea]

Hi there everybody,
I need some insight on sqlite limitation or different ways to save and sort data on sqlite.

I have to save in a sqlite table 2 dates (no time) and being able to sort the query results by date

I checked the sqlite and the clock instructions and I arrived to 2 options but implemented only one because I found quicker to code and store

1. ACTUAL SOLUTION: to be able to sort the query with the least of complications, I thought to save the dates as milliseconds as integer in SQLite. Millis are easy to convert to any format (one block). At this point, for reasons I don't understand the number saved are random and different from the insert/update query I use. I thought to be an INTEGER limitation but can't say for sure. Shall I use REAL insted? Is this the issue?

2. Option 2 not implemented because lengthy process to test a new option if #1 solution can work somehow: Use DATE function of SQLite. This option I'm almost sure works with ORDER option in SELECT query. The counterside is that I have to manipulate strings several time (showing date in the app, db saving format, convert db date format to app date format)

Any idea or suggestion to solve the issue with #1 or consider other possibilities?

[TIMAI2]

Option 1, as you say, is easier and should be less problematic. In sqlite it should work as an integer. Show your blocks for creating the value to be stored in sqlite. Cannot understand why it would be "random".

[Taifun]

I would prefer the readability of the data in the database

Help ordering a result by date in SQLite

Taifun

[gipsea]

@Taifun I checked the sqlite date options. a real changer would be if clock extension could add julian option which is more used out there. For sure a readable solution could be great but I only need it in dev stage. once in production level nobody is going to check the db. I have only to dip into date format output strftime() options and if still usable to order.

@TIMAI2 I'll pass some pics as soon as I can sit behind the pc. Starting point is to confirm clock millis produce number/integer and not a string. Also one check I'll try to do later on

[Taifun]

It's realy no big deal to write a procedure, which converts the date from and to a format of your choice, for example to ISO-8601 date (e.g., 2024-10-24).

Taifun

[gipsea]

@Taifun Correct, not a big deal...after I get a little more familiar with sqlite date. thanks anyway

@TIMAI2
sql insert query:

sql select query:

we have to take in consideration col 4 and 5.
Col4, most of the time is similar, col5 sometime is negative

checking both options at the moment

[TIMAI2]

I remembered the issue with SimpleSqlite you can read about it here:

[FREE] SimpleSqlite - an extension for working with SQLite Databases

OK, I am seeing that output too, tested with both commands: and Don't have that issue when running the query on SQLITE Browser app though [image] investigating....

Store your millis value as a string (so set the table column to TEXT)

e.g.

CREATE TABLE IF NOT EXISTS "millisTest" ("id" INTEGER NOT NULL, "millis" TEXT, PRIMARY KEY("id"))

INSERT INTO millisTest(millis) VALUES("1729784461149")

SELECT * FROM millisTest

output: [[1, "1729784461149"]]

Problem solved, AI2 will handle the string value as a number.

[edit - I have now added this quirk to my guide: Working with SQLite]

[gipsea]

@TIMAI2

@TIMAI2

CREATE TABLE IF NOT EXISTS "millisTest" ("id" INTEGER NOT NULL, "millis" TEXT, PRIMARY KEY("id"))

INSERT INTO millisTest(millis) VALUES("1729784461149")

SELECT * FROM millisTest

text will also work for order purpose I guess. Correct?

I didn't check but then when I retrieve the TXT I have to convert to number (millis) in AI2. At first quick look I didn't see a block for such conversion but can't say 100%. I did consider the txt option but didn't see the conversion block

cheers

[TIMAI2]

@gipsea

I didn't check but then when I retrieve the TXT I have to convert to number (millis) in AI2

No you don't, as I said above:

@TIMAI2

AI2 will handle the string value as a number