Sqlite: Current_Timestamp Is in Gmt, Not the Timezone of the MAChine

Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text:

Compute the date and time given a unix
timestamp 1092941466, and compensate
for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

That didn't look like it fit my needs, so I tried changing the "datetime" function around a bit, and wound up with this:

select datetime(timestamp, 'localtime')

That seems to work - is that the correct way to convert for your timezone, or is there a better way to do this?

How to set timezone in SQLite?

Don't store values with timezone in the first place. Instead, normalize your data to UTC and use UTC consistently in your queries.

Only when formatting the data for display purposes, adjust for user's local timezone.

Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text:

Compute the date and time given a unix
timestamp 1092941466, and compensate
for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

That didn't look like it fit my needs, so I tried changing the "datetime" function around a bit, and wound up with this:

select datetime(timestamp, 'localtime')

That seems to work - is that the correct way to convert for your timezone, or is there a better way to do this?

Store in UTC in SQLite and display in local timezone with PHP

As suggested by a comment, this page mentions the localtime modifier. Here is a solution:

$results = $db->query('SELECT datetime(date, "localtime") AS localdate, foo FROM test ORDER BY date desc');
while ($row = $results->fetchArray()) {
    echo $row['localdate'];
}

Android SQL DATETIME CURRENT_TIMESTAMP

I think DATETIME DEFAULT (datetime('now','localtime')) will do what you want and will be sortable it is stored as/like 2017-01-12 07:33:10.

i.e.

public void onCreate(SQLiteDatabase db) {
   String query = "CREATE TABLE " + TABLE_BLOCKEDSMS + " (" +
            COLUMN_BLOCKEDSMS + " TEXT," +
            COLUMN_DATETIME + "DATETIME DEFAULT (datetime('now','localtime')));";
    db.execSQL(query);
}

As per the comments the part of the statement COLUMN_DATETIME + "DATETIME DEFAULT (datetime('now','localtime'))); is explained as follows:-

COLUMN_DATETIME is the name of the column, DATETIME is the type of the column (noting that SQLite does not have rigid types Datatypes In SQLite Version 3 ). DEFAULT indicates the default value to be used (optional).

If the value is explicit e.g 0 'A' etc, then the value simply follows DEFAULT. However, if the value is calculated via an expression then it must be enclosed in brackets (). SQLite has numerous functions of which 5 are date and time functions. These are date, time, datetime, julianday and strftime.

Either datetime or strftime could be used, although datetime was chosen for this example/solution. So hence we have ... DEFAULT ( datetime(<argumnets>)) where haven't as yet been explained.

The syntax for datetime is datetime(timestring, modifier, modifier, ...). Timestring being the time (inclusion of date allowed). now generates a time timestring according to the current datetime. However, it is UTC. The localtime modifer, modifies the time according to local time (NOTE assuming that the timestring is UTC with caveat of unpredictable results if not).

Further and more detailed information can be found here SQL As Understood By SQLite - Date And Time Functions

---

Related Topics