Android Question Conversion of Date and Time across Timezones and database storage

fikayo

Member
The project I am designing (finance related) has these requirements:
1. Client organisation has location in 3 different timezones (none is utc/gmt)
2. Users in each location will work in their local times but the app must save all transactions with the local values converted to head-office datetime values.
3. Retrieved values will again be presented on-screen in the original local values.

I have tried to look at the methods of DateTime api and DateUtils library especially SetTimeZoneOffset() and SetDateAndTime2() but I don't know if they will suffice or there are some gotchas.ut I'm not sure how I can plan such a scenario. There could be problems with daylightsavings too.

I will appreciate any guidance and possibly code snippets.
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
The best way is to use a date format that includes the date, time and the timezone.

 
Upvote 0

RickV

Member
The project I am designing (finance related) has these requirements:
1. Client organisation has location in 3 different timezones (none is utc/gmt)
2. Users in each location will work in their local times but the app must save all transactions with the local values converted to head-office datetime values.
3. Retrieved values will again be presented on-screen in the original local values.

I have tried to look at the methods of DateTime api and DateUtils library especially SetTimeZoneOffset() and SetDateAndTime2() but I don't know if they will suffice or there are some gotchas.ut I'm not sure how I can plan such a scenario. There could be problems with daylightsavings too.

I will appreciate any guidance and possibly code snippets.
I have similar situation. Did you get it resolved?
How did you do it? if you dont mind sharing of course.....
 
Upvote 0

Chris2

Active Member
Licensed User
but the app must save all transactions with the local values converted to head-office datetime values.
Is this essential for you too, @RickV ?
As an alternative, could you store the datetimes in gmt/utc (or ticks) and allow the DateTime & DateUtils methods to convert to local datetime where ever the app is being used?
 
Upvote 0

RickV

Member
Is this essential for you too, @RickV ?
As an alternative, could you store the datetimes in gmt/utc (or ticks) and allow the DateTime & DateUtils methods to convert to local datetime where ever the app is being used?
I am using a mysql database server. I have decided to use a field with a long data type to store the ticks unless there is a better data type to use for storage and searching.

I have discovered that mysql has infact a perfect time zone query that returns the result that I have been looking for. I only discovered its potential this arvo.
You will need to populate the time zone tables in the mysql database and use the following queries (I wish I knew this 6 weeks ago haha)

SELECT CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York');
or
SELECT CONVERT_TZ('2023-05-05 17:00:00', '+10:00', '-5:00');

SELECT CONVERT_TZ('date time , from, to);

the result is a date time string value

If you dont have data in any of the timezone tables, use this link to get the data, open it with navicat or something and run the query, it will populate the database. It is about 5 MB

Use this if you want to pull the zones for a country
select * from time_zone_name where name like '%australia%';
 
Upvote 0
Top