Hello my friends and specialists
Please give me a hint:
Is there a extension to handle dates?
I mean: There is no datatype for date in sqlite. So I havve to store them as strings („01.09.2025“).
I want to make some mathematical operations with the data. i.e. adding or subtracting days like: 30.01.2025 + 5 = 04.02.2025
Thanx very much for your help
Emanuel
Look in the Sensors Drawer of the Designer for the Clock component.
It has blocks for all sorts of conversions.
For data base storage of dates and for date math, try the Milliseconds From 1970 (Clock1.Systemtime) value. It's a pure number, so it can be used in math operations.
5 days = 5 * 1000 * 60 * 60 * 24 milliseconds.
Don't try to store Instants in data bases.
They're lumpy and don't fit.
Just use milliseconds.
This is what Gemini us suggesting
Taifun
To subtract days from a date in SQLite, you can use the julianday function, the strftime function with a negative modifier, or the datetime() function with a negative modifier.
Using the julianday function
You can subtract a number of days from a date by converting the date to a Julian day number, subtracting the number of days, and then converting it back to a date using datetime(). This method is useful for subtracting a variable number of days.
The following syntax subtracts 10 days from the current date:
SELECT datetime(julianday('now') - 10);
Using the strftime or datetime function with a modifier
The strftime and datetime functions can take a modifier as an argument to change a date. To subtract a number of days, you can provide a negative number followed by the unit (e.g., '-10 days'). This is the most common and readable method.
This example subtracts 7 days from the date '2025-09-01':
SELECT datetime('2025-09-01', '-7 days');
You can use the same approach with the strftime function:
SELECT strftime('%Y-%m-%d', '2025-09-01', '-7 days');
Other modifiers
You can also subtract other units of time, such as months, years, or hours, using the same modifier syntax:
Better to store it in fornat yyyy-mm-dd
Taifun
Also:
and in case you are using the SimpleSQLite extension, see this:
(you can read the posts above this to fully understand what the issue was)
Thank You very much, my friends.
I will try then.
Wish you all a nice day
greetings Emanuel
Yes
What do you get? Show us your Do it result
And why are you wondering?
Taifun
Hi Taifun.
Thank you for everything.
Please take a look at the values in my last post.
morgen means tomorrow, …
Computers like numbers,
People like formatted date time values.
Who or what is reading your screen?
Look in the clock blocks for the conversion blocks.
Thank You very much TIMAI2.
I realized, that the best thing to stor a date, is integer (since 1970).
So I do so and write the big numbers in my db.
Getting it back to readable form, makes me problems.
I want to add two days to the big number and transform it into a readable datum. 29.09.2025 (i.e.)
Please tell me the function, that converts the integer: 1757196000000 to 07.09.2025.
Really? Let me respectfully disagree
This would be so simple with just a little bit sql
SELECT datetime('2025-09-07', '2 days');
Taifun
From what I see, you can replace all of this:
with this:
What do you wonder?
It's time you learned to read JSON.
Search the Web for 'introduction to JSON '
The JSON was telling you that you received a table from the sql query.
Prove me wrong.
Thank you very much ABG.
Now I know a bit of JSON.
But does it change anything?
There muSt be other methods to receive the dataa from the DB.
Please give me a hint.
ciao Emanuel
