How to insert date time into SQLite?

Efficiently managing datetime data is pivotal for many applications, from recording transaction timestamps to tracking events over time. Incorporating this information into an SQLite database not only ensures accurate data representation but also facilitates robust data analysis. The steps outlined below provide a comprehensive guide on how to seamlessly insert datetime data into SQLite, enhancing the overall functionality and usefulness of your database.

How to Setup?

To begin, Login to your Robomotion workspace. If you haven't registered yet, you can create your workspace from the provided link. Once you have access to your workspace, you can proceed by using the ‘Execute Non Query' node and ‘Insert table' node from the ‘SQLite' package.

About SQLite Package

• You can acquire this package through the 'Packages' menu, as illustrated in Figures 1 and 2.

Configuring 'Execute Non Query' Node: Input Specifications

• The name of the table for the 'Execute Non Query' connection ID must be entered in the Connection ID Box.
• he name of the table for the 'Execute Non Query' transaction ID must be entered in the Transaction Id Box.

Flow Steps

1. The 'Inject' node is used to initialize the flow.
2. Use the 'Function' node to create the SQLite path and connection string.

Code Block:

msg.dbFile = global.get("$Home$") + "/Desktop/data.db";
msg.dbConnStr = "Data Source=" + msg.dbFile + ";Version=3;";
return msg;

3. Use the 'Create Database' node to create a database. The 'Create Database' node has one input value, which is the directory path.

4. Use the 'Connect' node to connect to the database. The 'Connection' node has one 'Options' value for the connection string. The 'Options' should be filled.

5. Use the 'Execute Non Query' node to create a table.

CREATE TABLE IF NOT EXISTS 'products'
(
   'productTitle' TEXT, -- Column Type TEXT,
   'orderDate' DATETIME,
   'deliveryDate' DATETIME
);

6. Use the 'Function' node to create the input values.

msg.productTitle = "Pencil";
msg.orderDate = "2023-07-01";
msg.deliveryDate = "03.07.2023";
return msg;

7. Use the 'Execute Non Query' node to insert values into the database.

INSERT INTO products (productTitle, orderDate, deliveryDate)
VALUES('{{productTitle}}', '{{orderDate}}', substr('{{deliveryDate}}', 7, 4)||'-'||substr('{{deliveryDate}}', 4,2)||'-'||substr('{{deliveryDate}}', 1,2));

8. Stop the flow by using the 'Stop' node.

Here you can access the complete flow by clicking on the following link: https://casestudies.robomotion.io/designer/shared/Eb8LeD7rh65VGjSuNC3f62