How to Get Automatic Timestamp in SQLite

1. Add Timestamp to the SQLite Database
2. Use Datetime or Timestamp Datatype in SQLite
3. Retrieve DateTime From SQLite Table
4. Getting the Date and Time in SQLite
5. Text Storage Class for SQLite Date and Time

In C# language, we use SQLite database to store and retrieve values from the database. The database can contain different tables with rows and columns. Once we add a new record to the table, a new row is added with a timestamp as null. When we need a timestamp, we can see when a new row or particular information is added to the table with date and time-specific format. To get an automatic timestamp, we retrieve information as Datetime datatype or date and time as separate information.

Add Timestamp to the SQLite Database

The best solution to add a timestamp to the SQLite database is to add a field with Timestamp with the datatype DATETIME while creating the table in the database. The data type of the Timestamp should be DateTime in the SQLite database.

CREATE TABLE NameOfTheTable(
    myID INTEGER PRIMARY KEY,
    First_Name TEXT,
    Other FIELDS (if needed),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Output:

Creates the table with provided fields and last field timestamp

When there is a new entry inside this table, the table inserts a new row with the provided information and the current time inside the TimeStamp field. However, this field can be filled with NULL if the user explicitly sets it to a NULL value.

For example, we want to insert different rows into the created table above using the following commands.

INSERT INTO NameOfTheTable(First_Name, sqltime) VALUES('case1', '2020-07-28T15:36:56.200');
INSERT INTO NameOfTheTable(First_Name, sqltime) VALUES('case2', '2020-10-28T13:40:02.200');
# here, the time value will be recorded as NULL
INSERT INTO NameOfTheTable(First_Name) VALUES('case3');

Now, we need to use the SELECT command to show all the records inside the table.

SELECT * FROM NameOfTheTable;

Output:

1:case1:2020-07-28T15:36:56.200
2:case2:2020-07-28T15:36:56.200
3:case3:2022-11-11 05:38:20

Use Datetime or Timestamp Datatype in SQLite

In SQLite, when we need to track the changes inside the record, these records are updated with the timestamp datatype. So, if we want a specific time with a field, we need to use the DateTime datatype for a field.

We should always select the DateTime datatype whenever there is a comparison between the UNIX timestamp or a native SQLite DateTime field, as we can do the following calculations on the native DateTime datatype.

SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)

Using this command, we can also change the format of the DateTime to the UNIX timestamp.

SELECT UNIX_TIMESTAMP(my_datetime)

Retrieve DateTime From SQLite Table

When we insert the DateTime datatype into the SQLite database, it first converts it into the string type and then inserts it into the table. Similarly, when we fetch the data of the DateTime datatype, the sqlite3 module will first convert it into a string.

Let’s take an example to understand the concept. The function insertQuery() takes three parameters as id (primary key increments automatically when there’s a new record added to the table), my_name (non-empty string field to store names), and lastly, joining_date (with timestamp data type format).

Further, this program does not return the time in the string format but rather in the timestamp format.

import datetime
import sqlite3


def insertQuery(id, my_name, joiningDate):
    try:
        sqliteConnection = sqlite3.connect(
            "SQLite.db", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
        )
        cursor = sqliteConnection.cursor()
        print("SQLite Database is connected.")

        # query to create a new table with three fields
        sqlite_create_table_query = """CREATE TABLE new_table (
                                       id INTEGER PRIMARY KEY,
                                       my_name TEXT NOT NULL,
                                       joiningDate timestamp);"""

        cursor = sqliteConnection.cursor()
        cursor.execute(sqlite_create_table_query)

        # insert the new table information here
        sqlite_insert_with_param = """INSERT INTO 'new_table'
                          ('id', 'my_name', 'joiningDate')
                          VALUES (?, ?, ?);"""

        data_tuple = (id, my_name, joiningDate)
        cursor.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit()
        print("New record added successfully \n")

        # to get the timestamp information from the table
        sqlite_select_query = (
            """SELECT my_name, joiningDate from new_table where id = ?"""
        )
        cursor.execute(sqlite_select_query, (1,))
        records = cursor.fetchall()

        # the return type of datetime is not string now.
        for row in records:
            developer = row[0]
            joining_Date = row[1]
            print(developer, " joined on", joiningDate)
            print("Date type is ", type(joining_Date))

        cursor.close()

    # if an exception is found in sqlite version 3.
    except sqlite3.Error as error:
        print("Error with SQLite database", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("Closed connection for SQLite database.")


insertQuery(0, "NewName", datetime.datetime.now())

Output:

SQLite Database is connected.
New record added successfully

NewName  joined on 2019-06-28 20:57:32.352790
Date type is <class 'datetime.datetime'>
Closed connection for SQLite database.

Getting the Date and Time in SQLite

For Unix timestamp, the function in SQLite DATETIME() can be used with an argument unixepoch to compute the actual date and time. Alternatively, we can use the DATE() function to get the date information only and the TIME() function to return the time section part.

Get the Date and Time

SELECT DATETIME(1793956207, 'unixepoch');

Output:

2022-11-06 10:09:03

Get the Date

SELECT DATE(1793956207, 'unixepoch');

Output:

2022-11-06

Get the Time

SELECT TIME(1793956207, 'unixepoch');

Output:

10:09:03

Text Storage Class for SQLite Date and Time

In SQLite, we use the built-in methods to handle date and time within the database. Unlike SQLite, where they have built-in classes to interact with them, SQLite database has date and time built-in functions for their support.

When we use the TEXT storage class for setting the date and time in the SQLite database, we need to use the following format.

YYYY-MM-DD HH:MM:SS.SSS

This format results in 2019-02-02 11:21:08.321.