Example Codes for Handling Timezones in SQLite

• SQLite itself doesn't store timestamps with timezone information. It treats them as simple dates and times.
• When you use CURRENT_TIMESTAMP to insert a timestamp, it's automatically stored in UTC (GMT). This ensures consistency regardless of the server's location.

Why Use UTC for Storage?

• Storing timestamps in UTC makes it easier to manage data if the database is ever moved to a different time zone.
• It avoids confusion when working with data from users in various locations.

Working with Timezones in Queries

• You can't directly change how CURRENT_TIMESTAMP stores data.
• To display or use timestamps in a specific time zone, you can convert them within your queries using functions like datetime with the localtime modifier.
  • This converts the UTC timestamp to the local time zone of the server running the query.

Here's an example:

SELECT datetime('now', 'localtime');

This query retrieves the current timestamp and converts it to the server's local time zone.

Keep in Mind:

• localtime only considers the server's time zone, not necessarily the user's location.
• For user-specific time zones, you might need to store an additional time zone offset value with the timestamp or handle the conversion in your application.

Example Codes for Handling Timezones in SQLite

Inserting a timestamp in UTC (GMT):

INSERT INTO my_table (created_at)
VALUES (CURRENT_TIMESTAMP);

This code inserts the current timestamp in UTC (GMT) into the created_at column of the my_table.

Converting UTC timestamp to local time:

SELECT id, datetime('now', 'localtime') AS local_time
FROM my_table
WHERE id = 1;

This code retrieves the record with id = 1 from my_table. It then uses datetime('now', 'localtime') to convert the UTC timestamp stored in the database to the local time zone of the server running the query.

SELECT id, strftime('%Y-%m-%d %H:%M:%S', datetime('now', '+05:30')) AS india_time
FROM my_table
WHERE id = 1;

This code retrieves the record with id = 1 and converts the UTC timestamp to India Standard Time (IST) which is UTC+5:30.

• strftime('%Y-%m-%d %H:%M:%S', ...) formats the converted timestamp into a human-readable format.

Selecting timestamps within a specific time range (UTC):

SELECT *
FROM my_table
WHERE created_at BETWEEN strftime('%Y-%m-%d %H:%M:%S', '2024-04-07 00:00:00') AND strftime('%Y-%m-%d %H:%M:%S', '2024-04-07 23:59:59');

This code retrieves all records from my_table where the created_at timestamp (which is stored in UTC) falls between April 7th, 2024, 00:00:00 UTC and April 7th, 2024, 23:59:59 UTC.

• Alongside the UTC timestamp, store an additional column for the user's timezone offset from UTC (e.g., +05:30 for IST).
• During data retrieval, adjust the UTC timestamp by the user's timezone offset in your application logic before displaying or using it.

Use an ORM (Object-Relational Mapper):

• If you're using an ORM like SQLAlchemy with SQLite, it often provides built-in functionality for handling timezones.
• The ORM can manage timezone conversions between your application and the database, ensuring timestamps are stored in UTC and retrieved with the appropriate conversion.

External Timezone Service:

• Integrate an external service like a Timezone API into your application.
• When a timestamp needs conversion, use the user's location or IP address to query the service for the relevant timezone offset.
• Apply the offset to the UTC timestamp retrieved from SQLite within your application.

Choosing the Right Method:

• The best method depends on your specific needs and application complexity.
• Storing timezone offset offers more flexibility for user-specific conversions.
• ORMs simplify development but might introduce additional dependencies.
• External services can handle complex timezone rules but require additional setup and potential costs.