Sql – How to insert datetime with timezone to SQLite

datetimesqlsqlitetimestamptimezone

I know that to insert a datetime this format yyyy-mm-dd hh:mm:ss should be used.

However my dataset has a timestamp field that looks like yyyy-mm-dd hh:mm:ss +/-0X:00, where X can take many values and is different from my computer's local timezone.

What is the best way to insert a datetime field with such timezone information to SQLite?

Best Answer

SQLite's built-in date and time functions understand the timezone specification in these strings, but with different timezones, any other operations on these strings (even searches and comparisons) will not work correctly.

If you want to handle time zones, you have to either

convert all timestamps to one specific time zone (UTC), so that you can use them for sorting and searching; or
leave the time zone information in there, but do all searching, sorting and other computations not in SQL but in your application.

Related Solutions

C# – How to calculate someone’s age based on a DateTime type birthday

An easy to understand and simple solution.

// Save today's date.
var today = DateTime.Today;

// Calculate the age.
var age = today.Year - birthdate.Year;

// Go back to the year in which the person was born in case of a leap year
if (birthdate.Date > today.AddYears(-age)) age--;

However, this assumes you are looking for the western idea of the age and not using East Asian reckoning.

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Best Answer

Related Solutions

C# – How to calculate someone’s age based on a DateTime type birthday

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Related Topic