# SQL lite datetime field or rather use SQL

**URL:** <https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531>\
**Category:** General\
**Created:** [6 June 2024 08:11 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531 "2024-06-06T08:11:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marius1](https://avatars.discourse-cdn.com/v4/letter/m/a698b9/32.png) [@Marius1](https://discourse.nodered.org/u/Marius1)\
**Post date:** [6 June 2024 08:11 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531/1 "2024-06-06T08:11:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2024 08:33 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531/2 "2024-06-06T08:33:27Z")

</div>

> [@Marius1](#):
>
> 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

Explain the types you tried and the problems you faced.

> [@Marius1](#):
>
> What is the best way to do this or should I rather try to use SQL?

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.

> [@Marius1](#):
>
> 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.

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?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [6 June 2024 12:22 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531/3 "2024-06-06T12:22:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AndrewBeasley](https://avatars.discourse-cdn.com/v4/letter/a/f17d59/32.png) [@AndrewBeasley](https://discourse.nodered.org/u/AndrewBeasley)\
**Post date:** [7 June 2024 00:48 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531/4 "2024-06-07T00:48:21Z")

</div>

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 🙂

Function details can be found at [Date And Time Functions](https://www.sqlite.org/lang_datefunc.html) 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).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [5 September 2024 00:49 UTC](https://discourse.nodered.org/t/sql-lite-datetime-field-or-rather-use-sql/88531/5 "2024-09-05T00:49:02Z")

</div>

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