SQLite and dates

[Emanuel_Kury]

@Taifun

datetime('2025-09-07', '2 days');

@‚Taifun:

select Text, datetime(Datum, '-2 days') from Reminder where Status = 0 order by Datum

gives a null value:

[Taifun]

@Emanuel_Kury

select Text, datetime(Datum, '-2 days') from Reminder where Status = 0 order by Datum

gives a null value:

And what do you get here

select Text, Datum from Reminder where Status = 0

Also provide your CREATE TABLE statement

Taifun

[Emanuel_Kury]

CREATE TABLE IF NOT EXISTS 'Reminder' ('id' INTEGER NOT NULL, 'Text' TEXT, 'vor' INTEGER, 'Status' INTEGER, 'Datum' TEXT, 'TST' TEXT, PRIMARY KEY('id'))

[Taifun]

@Taifun

And what do you get here

select Text, Datum from Reminder where Status = 0

Taifun

[Emanuel_Kury]

I get ‚null‘

[Taifun]

You already said that earlier, the question is

@Taifun

And what do you get here

select Text, Datum from Reminder where Status = 0

Taifun

[Emanuel_Kury]

@Taifun

select Text, Datum from Reminder where Status = 0

just a text field with a date

[Taifun]

The format of your Datum should be yyyy-mm-dd

@Taifun

Better to store it in fornat yyyy-mm-dd

Taifun

[Emanuel_Kury]

[Professional]

@Emanuel_Kury
You can use my extension

[Free] Date&TimeTools extension

Write a short description DateAndTimeTool A comprehensive utility class providing various methods to manipulate date and time settings on android devices and also calculate dates and times. This tool offers functionalities to: Support online time by Timzoneoffset Compare dates (before, after, equal) Add days to a date Convert milliseconds to time format Convert days to month format Check leap years Get days in a month Convert Unix timestamps to dates and vice versa Get current date, time, and…

[ABG]

When my wife opens a box of pasta, she dumps it out onto a sheet of paper to check for creepy crawlers.

Only after verifying it is good, she pours it into the pot.
(She doesn't throw the box into the pot, as far as I can tell)

A good coder does the same with Web and data base output.

Use a global variable for the sheet of paper.

Here are some good tests:

• Is a list?

• Length of list greater than (0/1/2/...)?

• Length of list of list item n?

[Taifun]

To get leading zeros for 1 digit months and days you could use this procedure

How to force added zero to format as decimal

as you can see from the example screenshot, the procedure works for 1 digit numbers, however for number with decimals you have to adjust it to your needs... [2leadingZeros] The example uses the exponential operation, see also Exponents The exponent of a number says how many times to use the number in a multiplication. example number=7, digits=4: 104+7=10007, now remove the first digit to get the result, which is 0007 example number=123, digits=4: 104+123=10123, now remove the first digi…

Taifun

[ABG]

@Emanuel_Kury

Now I know a bit of JSON.
But does it change anything?

Ai2 by default renders lists and dictionaries as JSON text when you ask to output them in human readable form.

JSON gives you hints as to the original structure of the list or dictionary.
The nested [ marks tell you if it was a list. Nested [[ tells you it was a list of lists. Quote marks versus numbers without quote marks tell you if it was a number or text. { marks tell you it was a dictionary.

So if you return raw web or database returns into a Label.Text or the Do It of a global variable (better), you will have the mental tools needed to read and decipher the internal structure of what was returned.

[ABG]

@ABG

the mental tools

• a sharp eye, willing and able to spot what doesn't belong
• imagination to visualize the flow of data over time and space
• a mental catalog of the different ways data can assemble into data structures
• a vocabulary to describe things and their attributes
• numeracy, to distinguish between one and many
• a firm grasp of Murphy's Law, and willingness to address the possibility that things can go wrong
• courage to read code all the way through
• knowing when to go back and read the docs

(What did I miss?)