0 0 Votes

SQLite: CURRENT_TIMESTAMP returns wrong Time / Hour

Question by SmartUser | 28/03/2017 at 21:52

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?

ReplyPositiveNegative

Stefan Trost

Show Profile | Message
Avatar
2Best Answer
2 Votes

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:

SELECT DATETIME(dat, 'localtime') FROM tab;

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.
29/03/2017 at 20:01

ReplyPositive Negative
Reply

Related Topics

PHP: Current Date and Time
Tutorial | 0 Comments

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 articles on askingbox.com. How to do.