Insert the date and time separately into an SQL query

General
,
1

Hello,
Excuse me for this naive question, but I'm a beginner.
Here's my problem: I have a sensor that sends me speed information when an object passes in front of it. I want to record the date, time (in different columns), and speed in a MySQL database. Currently, I record the speed and the date and time with curtime (but in the same column).
My question: how can I have two variables with the date and time to insert them into my SQL query?
Thanks for your help.
Jean

2

You can route the speed info through a function node, and then use standard JavaScript to get the current date & time.
for example:

const now = new Date();
const currentDate = now.toLocaleDateString(); // Gets the current date
const currentTime = now.toLocaleTimeString(); // Gets the current time

Alternatively, you can use a change node with JSonata expressions:

$fromMillis($millis(), "[Y0001]-[M01]-[D01]")
$fromMillis($millis(), "[H01]:[m01]:[s01]")
3

And don't forget you can store date and time as you do now but extract date and time seperately in a SELECT statement.
In my database, the timestamp field has a default value of CURRENT_TIMESTAMP

MariaDB [espresso]> SELECT timestamp, DATE(timestamp) as date, TIME(timestamp) as time FROM test;
+---------------------+------------+----------+
| timestamp           | date       | time     |
+---------------------+------------+----------+
| 2024-12-27 17:15:00 | 2024-12-27 | 17:15:00 |
| 2024-12-28 05:45:00 | 2024-12-28 | 05:45:00 |
| 2024-12-28 18:15:01 | 2024-12-28 | 18:15:01 |
| 2024-12-29 00:30:01 | 2024-12-29 | 00:30:01 |
1 Like
4

I'd be interested in what sensor you are using for that?

5

Thanks, so no need to memorize date and time separately.

6

It's an Omnipresense OPS243

1 Like
7

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