Android Question Problem among timezone and current_timestamp

vecino

Well-Known Member
Licensed User
Longtime User
Hi, I have the following problem:

My timezone is GMT +2
A field of a table has default CURRENT_TIMESTAMP

The table must be saved GMT +2
However, keep GMT

What I can do?
 

Attachments

  • 1-timezone.png
    1-timezone.png
    26.6 KB · Views: 262
  • 2-date-command.png
    2-date-command.png
    23.3 KB · Views: 239
  • 3-table-structure.png
    3-table-structure.png
    21.5 KB · Views: 229
  • 4-insert-row.png
    4-insert-row.png
    27.9 KB · Views: 260
  • 5-result.png
    5-result.png
    12.6 KB · Views: 218

eps

Expert
Licensed User
Longtime User
Correct. Current Time (timezone 0) is 15:40. You see 15:40 + 2 i.e. 17:40. You need to store the Timezone difference for the User and then 'add' it to the Time retrieved.

TimeZone offset or something similar... I think.
 
Upvote 0

eps

Expert
Licensed User
Longtime User
I think you need to be clear about what you are doing and what information you need to store along with the Time... Unfortunately it's a little more complicated than just adding timezones.

i.e. time is stored (timezone 0?) or possibly timezone with a local timezone and then displayed with a remote timezone to take in to account.. Without more information about what you're trying to achieve it's a bit hard to help.
 
Upvote 0

vecino

Well-Known Member
Licensed User
Longtime User
Hi, thanks for replying.

If they are 17:32
I think you should record in the database: 17:32
But save 15:32
2 hours ahead of GMT

Let me explain:

Madrid, GMT +2
It's 17:00 local time.
In the database, when a record is saved, "CURRENT_TIMESTAMP" stores 15:00
What I can do to "CURRENT_TIMESTAMP" store 17:00?
 
Last edited:
Upvote 0

eps

Expert
Licensed User
Longtime User
It may well be that you need to look a little further in to sqlite I'm not sure if it can handle the timezone or you take the calculation out of the sql statement and then process it in B4A and then pump the value in to sqlite. It's not something I've had to consider.
 
Upvote 0

vecino

Well-Known Member
Licensed User
Longtime User
Hi, I think I've found the solution.

Example:
B4X:
CREATE TABLE tbXXX
(
  id integer not null primary key autoincrement,
  tipo integer not null default 0,
  fechahoraold timestamp default (datetime('now','localtime')),
  fechahoranew timestamp default CURRENT_TIMESTAMP
)
Using (datetime('now','localtime')) is stored in the database local time.
 
Upvote 0
Top