SQL lite datetime field or rather use SQL

General
1

Morning.
I am trying to do a program with a database. One of the tables should be a datetime field Currently I am using sql lite as my database but it does not have a datetime field. I used couple of different fiels to store the information but it is giving me trouble if I want to sort my data by the date time field. I suspect it might be because it is not a datetime field. I cant remember what is all the different field types and date formats that I have tried sovar.

What is the best way to do this or should I rather try to use SQL?
The program wil run on a PI so I am also not sure if SQL server can run on a PI?
I have also started to create an sql database to test with but I cant connect to the sql database on node red. I can connect to it with ssms. The sql server is running on the local pc where node red is. I get error "Error: connect ETIMEDOUT" or "AggregateError: AggregateError"

Thanks in advance for any assistance

2

Explain the types you tried and the problems you faced.

I assume you mean MS SQL Server.

It all depends. if SQLite is enough for the task, then using SQL Server is very much OTT.

Tell us which node you are using. The better (more featureful, newer, supported, less buggy) node of choice for MS SQL Server is the node-red-contrib-mssql-plus node.

Also,

  • is the SQL Server an instance or default install?
  • have you enabled SQL Authentication
  • Is it on the same computer as different
    • if different, is there a firewall preventing connection?
3

You can store date/time in sqlite either as an integer (a Unix or JavaScript timestamp) or as a string in iso format "2024-06-30T13:27:24.097T"

Both of these formats are sortable.

You can obtain the date or time portion using the date() and time() functions.
I think you have to specify 'unixepoch' of the field is an integer.

If you prefer a more substantial database, mariadb (mysql) works well on a raspberry pi.
(I run it on a Pi 4)

ps In my sqlite I have fields defined as DATETIME. I'm pretty sure it's a synonym for INTEGER.
I store JavaScript timestamps and I can access the time part with SELECT time(timestamp/1000, 'unix_epoch') FROM mytable. That's from memory, might be wrong.

4

SQLite is quite good at handling dates and times using the ISO 8601 standard format (YYYY-MM-DD HH:MM:SS) though it can go to sub seconds (SS.XXX) if needed (not something I've ever done though).

I find it best to use a text field (column) for values as the majority of the inbuilt functions can use these and looking at the values by eye is way way easier than numeric values :slight_smile:

Function details can be found at Date And Time Functions on the SQLite site.

Remember you can create indexes over multiple columns e.g.:

Data records are stored in the 'records' table and the entry date and time are recorded in YYYYMMDD and HHMMSS string format (or similar)

CREATE INDEX idx_bytime ON records (rec_date, rec_time);

This index will be used for select statements using WHERE clauses with rec_date = "YYYYMMDD" or WHERE clauses with rec_date and rec_time is used with an AND BUT it is not used if only rec_time is in the WHERE clause or where the WHERE clause is an OR select - i.e.

WHERE rec_date = 'YYYYMMDD' AND rec_time = 'HHMMSS'
WHERE rec_date > 'YYYYMMDD'
will use the index but

WHERE rec_time = 'HHMMSS'
WHERE rec_date = 'YYYYMMDD' OR rec_time = 'HHMMSS'
will not.

Hope that makes sense - pop some data on here if not and I can create an example for you (though I'm not always here so it may take some time).

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.