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

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

fikayo said:

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.

Click to expand...

I have similar situation. Did you get it resolved?
How did you do it? if you dont mind sharing of course.....

fikayo said:

but the app must save all transactions with the local values converted to head-office datetime values.

Click to expand...

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?

Chris2 said:

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?

Click to expand...

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%';