Marca de tiempo automática en SQLite

1. Agregar marca de tiempo a la base de datos SQLite
2. Use Datetime o Timestamp Datatype en SQLite
3. Recuperar DateTime de la tabla SQLite
4. Obtener la fecha y la hora en SQLite
5. Clase de almacenamiento de texto para fecha y hora de SQLite

En lenguaje C#, usamos la base de datos SQLite para almacenar y recuperar valores de la base de datos. La base de datos puede contener diferentes tablas con filas y columnas. Una vez que agregamos un nuevo registro a la tabla, se agrega una nueva fila con una marca de tiempo como nula. Cuando necesitamos una marca de tiempo, podemos ver cuándo se agrega una nueva fila o información particular a la tabla con formato específico de fecha y hora. Para obtener una marca de tiempo automática, recuperamos información como tipo de datos Datetime o fecha y hora como información separada.

Agregar marca de tiempo a la base de datos SQLite

La mejor solución para agregar una marca de tiempo a la base de datos SQLite es agregar un campo con Timestamp con el tipo de datos DATETIME al crear la tabla en la base de datos. El tipo de datos de Timestamp debe ser DateTime en la base de datos SQLite.

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

Producción :

Creates the table with provided fields and last field timestamp

Cuando hay una nueva entrada dentro de esta tabla, la tabla inserta una nueva fila con la información proporcionada y la hora actual dentro del campo TimeStamp. Sin embargo, este campo se puede completar con NULL si el usuario lo establece explícitamente en un valor NULL.

Por ejemplo, queremos insertar diferentes filas en la tabla creada arriba usando los siguientes comandos.

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');

Ahora, necesitamos usar el comando SELECCIONAR para mostrar todos los registros dentro de la tabla.

SELECT * FROM NameOfTheTable;

Producción :

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 o Timestamp Datatype en SQLite

En SQLite, cuando necesitamos realizar un seguimiento de los cambios dentro del registro, estos registros se actualizan con el tipo de datos de marca de tiempo. Entonces, si queremos una hora específica con un campo, necesitamos usar el tipo de datos DateTime para un campo.

Siempre debemos seleccionar el tipo de datos DateTime siempre que haya una comparación entre la marca de tiempo de UNIX o un campo DateTime nativo de SQLite, ya que podemos hacer los siguientes cálculos en el tipo de datos nativo DateTime.

SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)

Usando este comando, también podemos cambiar el formato de DateTime a la marca de tiempo UNIX.

SELECT UNIX_TIMESTAMP(my_datetime)

Recuperar DateTime de la tabla SQLite

Cuando insertamos el tipo de datos DateTime en la base de datos SQLite, primero lo convierte en el tipo de cadena y luego lo inserta en la tabla. De manera similar, cuando buscamos los datos del tipo de datos DateTime, el módulo sqlite3 primero los convertirá en una cadena.

Pongamos un ejemplo para entender el concepto. La función insertQuery() toma tres parámetros como id (la clave principal se incrementa automáticamente cuando se agrega un nuevo registro a la tabla), my_name (campo de cadena no vacío para almacenar nombres) y, por último, join_date (con formato de tipo de datos de marca de tiempo ).

Además, este programa no devuelve la hora en formato de cadena, sino en formato de marca de tiempo.

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())

Producción :

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.

Obtener la fecha y la hora en SQLite

Para la marca de tiempo de Unix, la función en SQLite DATETIME() se puede usar con un argumento unixepoch para calcular la fecha y la hora reales. Alternativamente, podemos usar la función DATE() para obtener solo la información de la fecha y la función TIME() para devolver la parte de la sección de tiempo.

Obtener la fecha y la hora

SELECT DATETIME(1793956207, 'unixepoch');

Producción :

2022-11-06 10:09:03

obtener la fecha

SELECT DATE(1793956207, 'unixepoch');

Producción :

2022-11-06

obtener el tiempo

SELECT TIME(1793956207, 'unixepoch');

Producción :

10:09:03

Clase de almacenamiento de texto para fecha y hora de SQLite

En SQLite, usamos los métodos incorporados para manejar la fecha y la hora dentro de la base de datos. A diferencia de SQLite, donde tienen clases integradas para interactuar con ellas, la base de datos SQLite tiene funciones integradas de fecha y hora para su soporte.

Cuando usamos la clase de almacenamiento TEXT para configurar la fecha y la hora en la base de datos SQLite, debemos usar el siguiente formato.

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

Este formato da como resultado 2019-02-02 11:21:08.321.