SQLite: CURRENT_TIMESTAMP returns wrong Time / Hour
Question by SmartUser | Last update on 2023-11-23 | Created on 2017-03-28
I would like to store the current time in a SQLite-database. For this, I have discovered the constant CURRENT_TIMESTAMP which should provide me with just the timestamp I need.
However, the time is always shifted by an hour. It does not matter whether I am working with a default column value...
CREATE TABLE d (dat DATETIME DEFAULT CURRENT_TIMESTAMP, ...);
...or I am setting the value "by hand":
UPDATE d SET dat = CURRENT_TIMESTAMP WHERE ...;
Both ways are leading to this timeshift and I always get the wrong time with that time difference when trying to read out the values. The strange thing about that is that the minutes are always correct, the hour is always wrong. What can I do?
Related Topics
MySQL: Write current Date or Time into Column
Tutorial | 0 Comments
SQLite: String and Column Concatenation using SQLite
Info | 0 Comments
PHP: Current Date and Time
Tutorial | 0 Comments
Debian: The package is of bad quality: wrong-file-owner-uid-or-gid
Question | 1 Answer
PHP: Check Strings with Ctype-Functions for Character Classes
Article | 0 Comments
SQLite: Error "no such function: INSTR"
Question | 1 Answer
Important Note
Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.
Participate
Ask your own question or write your own article on askingbox.com. That’s how it’s done.
SQLite is storing the CURRENT_TIMESTAMP time as UTC/GMT-time (Coordinated Universal Time / Greenwich Mean Time). This means that the time is stored independent from the corresponding time zone you are running your computer in.
Therefore, you have to convert the time into your current local time when reading it out, so that time zone and daylight saving time are considered.
You can do that in the following way:
In this example, we are reading out the column "dat" from the table "tab".
An advantage of this kind of storage is that the storage is always the same and not depending on the location or time zone.
Last update on 2023-11-23 | Created on 2017-03-29