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

[fikayo]

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]

The best way is to use a date format that includes the date, time and the timezone.

problem with TimeZone/Daylight

I have a date as string coming from a GPS system that need to be represented with the timezone and daylight saving settings. This is the code that works fine Dim dt As Long=DateTime.Dateparse("2018-03-25 02:01:22") dt=dt+(DateTime.GetTimeZoneOffsetAt(dt)*3600000)...

www.b4x.com

 

[RickV]

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.....

 

[Chris2]

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?

 

[RickV]

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

MySQL :: Time zone description tables

dev.mysql.com

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