Sqlite CURRENT TIMESTAMP is in GMT not the timezone of the machine

Question

I have a sqlite  v3  table with this column definition    timestamp  DATETIME DEFAULT CURRENT TIMESTAMP   The server that this database lives on is in the CST time zone   When I insert into my table without including the timestamp column  sqlite automatically populates that field with the current timestamp in GMT  not CST   Is there a way to modify my insert statement to force the stored timestamp to be in CST   On the other hand  it is probably better to store it in GMT  in case the database gets moved to a different timezone  for example   so is there a way I can modify my select SQL to convert the stored timestamp to CST when I extract it from the table

User · Answer

You can also just convert the time column to a timestamp by using strftime():

SELECT strftime('%s', timestamp) as timestamp FROM ... ;

Gives you:

1454521888

'timestamp' table column can be a text field even, using the current_timestamp as DEFAULT.

Without strftime:

SELECT timestamp FROM ... ;

Gives you:

2016-02-03 17:51:28

User · Answer

You should  as a rule  leave timestamps in the database in GMT  and only convert them to from local time on input output  when you can convert them to the user s  not server s  local timestamp   It would be nice if you could do the following   SELECT DATETIME col   PDT        to output the timestamp for a user on Pacific Daylight Time  Unfortunately  that doesn t work  According to this SQLite tutorial  however  scroll down to  Other Date and Time Commands    you can ask for the time  and then apply an offset  in hours  at the same time  So  if you do know the user s timezone offset  you re good   Doesn t deal with daylight saving rules  though

User · Answer

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

User · Answer

The current time  in your machine s timezone   select time time     localtime      As per http   www sqlite org lang datefunc html

User · Answer

SELECT datetime CURRENT TIMESTAMP   localtime

User · Answer

simply use local time as the default   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

When having a column defined with  NOT NULL DEFAULT CURRENT TIMESTAMP   inserted records will always get set with UTC GMT time   Here s what I did to avoid having to include the time in my INSERT UPDATE statements   --Create a table having a CURRENT TIMESTAMP  CREATE TABLE FOOBAR       RECORD NO INTEGER NOT NULL      TO STORE INTEGER      UPC CHAR 30       QTY DECIMAL 15 4       EID CHAR 16       RECORD TIME NOT NULL DEFAULT CURRENT TIMESTAMP   --Create before update and after insert triggers  CREATE TRIGGER UPDATE FOOBAR BEFORE UPDATE ON FOOBAR     BEGIN        UPDATE FOOBAR SET record time   datetime  now    localtime          WHERE rowid   new rowid      END  CREATE TRIGGER INSERT FOOBAR AFTER INSERT ON FOOBAR     BEGIN        UPDATE FOOBAR SET record time   datetime  now    localtime          WHERE rowid   new rowid      END   Test to see if it works     --INSERT a couple records into the table  INSERT INTO foobar  RECORD NO  TO STORE  UPC  PRICE  EID      VALUES  0  1   xyz1   31   777    INSERT INTO foobar  RECORD NO  TO STORE  UPC  PRICE  EID      VALUES  1  1   xyz2   32   777    --UPDATE one of the records  UPDATE foobar SET price   29 WHERE upc    xyz2   --Check the results  SELECT   FROM foobar   Hope that helps

User · Answer

In the  admitted rare  case that a local datatime is wanted  I  for example  store local time in one of my database since all I care is what time in the day is was and I don t keep track of where I was in term of time zones      you can define the column as    timestamp  TEXT DEFAULT  strftime   Y- m- dT H  M   now    localtime      The  Y- m- dT H  M part is of course optional  it is just how I like my time to be stored   Also  if my impression is correct  there is no  DATETIME  datatype in sqlite  so it does not really matter whether TEXT or DATETIME is used as data type in column declaration

User · Answer

You can also just convert the time column to a timestamp by using strftime():

SELECT strftime('%s', timestamp) as timestamp FROM ... ;

Gives you:

1454521888

'timestamp' table column can be a text field even, using the current_timestamp as DEFAULT.

Without strftime:

SELECT timestamp FROM ... ;

Gives you:

2016-02-03 17:51:28

User · Answer

SELECT datetime  now    localtime

User · Answer

You should  as a rule  leave timestamps in the database in GMT  and only convert them to from local time on input output  when you can convert them to the user s  not server s  local timestamp   It would be nice if you could do the following   SELECT DATETIME col   PDT        to output the timestamp for a user on Pacific Daylight Time  Unfortunately  that doesn t work  According to this SQLite tutorial  however  scroll down to  Other Date and Time Commands    you can ask for the time  and then apply an offset  in hours  at the same time  So  if you do know the user s timezone offset  you re good   Doesn t deal with daylight saving rules  though

User · Answer

You should  as a rule  leave timestamps in the database in GMT  and only convert them to from local time on input output  when you can convert them to the user s  not server s  local timestamp   It would be nice if you could do the following   SELECT DATETIME col   PDT        to output the timestamp for a user on Pacific Daylight Time  Unfortunately  that doesn t work  According to this SQLite tutorial  however  scroll down to  Other Date and Time Commands    you can ask for the time  and then apply an offset  in hours  at the same time  So  if you do know the user s timezone offset  you re good   Doesn t deal with daylight saving rules  though

User · Answer

SELECT datetime CURRENT TIMESTAMP   localtime

User · Answer

The current time  in your machine s timezone   select time time     localtime      As per http   www sqlite org lang datefunc html

User · Answer

In the  admitted rare  case that a local datatime is wanted  I  for example  store local time in one of my database since all I care is what time in the day is was and I don t keep track of where I was in term of time zones      you can define the column as    timestamp  TEXT DEFAULT  strftime   Y- m- dT H  M   now    localtime      The  Y- m- dT H  M part is of course optional  it is just how I like my time to be stored   Also  if my impression is correct  there is no  DATETIME  datatype in sqlite  so it does not really matter whether TEXT or DATETIME is used as data type in column declaration

User · Answer

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

User · Answer

I think this might help    SELECT datetime strftime   s   now     unixepoch    localtime

User · Answer

simply use local time as the default   CREATE TABLE whatever                 timestamp DATE DEFAULT  datetime  now   localtime

User · Answer

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

User · Answer

Time    now    localtime    and Date    now    localtime    works

User · Answer

I think this might help    SELECT datetime strftime   s   now     unixepoch    localtime

User · Answer

When having a column defined with  NOT NULL DEFAULT CURRENT TIMESTAMP   inserted records will always get set with UTC GMT time   Here s what I did to avoid having to include the time in my INSERT UPDATE statements   --Create a table having a CURRENT TIMESTAMP  CREATE TABLE FOOBAR       RECORD NO INTEGER NOT NULL      TO STORE INTEGER      UPC CHAR 30       QTY DECIMAL 15 4       EID CHAR 16       RECORD TIME NOT NULL DEFAULT CURRENT TIMESTAMP   --Create before update and after insert triggers  CREATE TRIGGER UPDATE FOOBAR BEFORE UPDATE ON FOOBAR     BEGIN        UPDATE FOOBAR SET record time   datetime  now    localtime          WHERE rowid   new rowid      END  CREATE TRIGGER INSERT FOOBAR AFTER INSERT ON FOOBAR     BEGIN        UPDATE FOOBAR SET record time   datetime  now    localtime          WHERE rowid   new rowid      END   Test to see if it works     --INSERT a couple records into the table  INSERT INTO foobar  RECORD NO  TO STORE  UPC  PRICE  EID      VALUES  0  1   xyz1   31   777    INSERT INTO foobar  RECORD NO  TO STORE  UPC  PRICE  EID      VALUES  1  1   xyz2   32   777    --UPDATE one of the records  UPDATE foobar SET price   29 WHERE upc    xyz2   --Check the results  SELECT   FROM foobar   Hope that helps

User · Answer

SELECT datetime  now    localtime

User · Answer

Time    now    localtime    and Date    now    localtime    works

User · Answer

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

User · Answer

You should  as a rule  leave timestamps in the database in GMT  and only convert them to from local time on input output  when you can convert them to the user s  not server s  local timestamp   It would be nice if you could do the following   SELECT DATETIME col   PDT        to output the timestamp for a user on Pacific Daylight Time  Unfortunately  that doesn t work  According to this SQLite tutorial  however  scroll down to  Other Date and Time Commands    you can ask for the time  and then apply an offset  in hours  at the same time  So  if you do know the user s timezone offset  you re good   Doesn t deal with daylight saving rules  though

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

Examples related to sql

Examples related to sqlite

Examples related to timezone

Examples related to timestamp