How do I insert datetime value into a SQLite database?

Stick to the golden format

In SQLite, the golden format for storing datetime values is YYYY-MM-DD HH:MM:SS. Get it right, and SQLite will behave like the best SQL database around!

Parameterized queries to the rescue!

Using parameterized queries helps prevent injection attacks and ensures the values are inserted correctly. Here's a summarized example in Python's sqlite3:

import sqlite3, datetime

conn = sqlite3.connect('example.db')
curs = conn.cursor()

now = datetime.datetime.now()
formatted_now = now.strftime('%Y-%m-%d %H:%M:%S')

curs.execute("INSERT INTO your_table (datetime_column) VALUES (?)", (formatted_now,))  -- "SQLite baby, I swear I trust you!"

conn.commit()
conn.close()


TEXT datatype: Your trusty steed

Opt for the TEXT datatype to store datetime values in SQLite. This will ensure compatibility across different databases and systems.

Decoding fractional seconds

SQLite also understands fractional seconds. Just add them during insertion, as follows:

INSERT INTO your_table (datetime_column) VALUES ('2023-04-01 14:30:00.123');  -- "Fooled ya, SQLite! I've got miliseconds too!"


Unleashing SQLite's datetime functions

SQLite comes with some power-packed datetime functions like STRFTIME, DATE, TIME, etc.

SELECT STRFTIME('%Y-%m-%d', datetime_column) as `date_only` FROM your_table;  -- "No time for time, just give me the date!"


Compatibility alert: Avoid MySQL habits

Remember, SQLite might be polite, but it doesn't understand MySQL’s NOW(). Stick to DATETIME('now') or CURRENT_TIMESTAMP.

Tackling retrieval issues

Encountered errors when retrieving data? Put on your detective cap and compare the stored format with the format used in the SELECT query.

A quick run-down: SQLite datetime functions

• DATETIME: Manage datetime values.
• STRFTIME: Custom-format your dates and times.
• DATE: Extract the date from a datetime.
• TIME: Extract only the time from a datetime.