Android Question Timestamp-problem

TheRealMatze

Active Member
Licensed User
Hi,
i just got response from one of my app-testers that sometimes the sync doesn´t work. After some investigation i see that the app thinks that the changes inside the app are older than on the server. I use a kind of timestamp based on utc to find out wich version is the current.
In the app i use
B4X:
Sub timestamp() As Int
    Dim r As ResultSet=sql1.ExecQuery("Select (strftime('%s','now','utc') - strftime('%s','2020-01-01 00:00:00', 'utc')) as x")
    r.NextRow
    Dim retstr As String=r.GetString("x")
    r.close
    Return retstr
End Sub
and on my sql-server
SQL:
select datediff(second,'01.01.2020',GETUTCDATE())

When i start the app in the emulator the timestamps are equal, but on my phone i have a gap of exact 1 hour.
The sql server is like the simulator in UTC, my phone in CEST = UTC+2. When i last checked the app says 71868408 but the server 71872021 (higher...)

I have absolutely no idea why there is a difference. Can anybody help me?

Regards
Matze
 

MicroDrie

Well-Known Member
Licensed User
Are you sure that '01.01.2020' is the right format? Should that not be '01-01-2020' to prevent a wrong result?
 
Upvote 0

emexes

Expert
Licensed User
The one hour difference makes me think that it might be a daylight saving time issue, eg on the east side of Australia we have timezones AEST (Australian Eastern Standard Time) and AEDT (Australian Eastern Daylight Time) and sometimes they are in synch (winter) and sometimes they are an hour apart (summer).

AEST is for the northern states close to the equator, which have daylight hours that don't change much and thus daylight saving is not of practical benefit* and not used.

AEDT is for the southern states away from the equator, which have significantly fewer daylight hours in winter and more in summer, and daylight saving shifts the bonus summer daylight towards the evening.

When AEST and AEDT times are aligned (during winter), it is easy for somebody to choose the "wrong" timezone because both local times are the same. The usual scenario six months later when daylight saving starts, is that the device time is then an hour different (either because it didn't adjust for daylight saving when it should have, or did adjust when it shouldn't have) and the user "fixes" the problem by turning off auto-time-synch and manually "correcting" the time.

My first check would be to synch the device time to an internet time server, and see if the device is then still showing correct local time. If it is not, then have a poke around in the device timezone selection/setting, looking for one that would correctly match UTC to local clock time.


* other than avoiding time differences esp. at state borders
 
Upvote 0

Chris2

Active Member
Licensed User
I might be on the wrong track here, but seeing you mention time zones & SQL Server made me think of the issues I've had retrieving UTC datetimes from SQL Server.
Could your issue have a similar cause?
 
Upvote 0

TheRealMatze

Active Member
Licensed User
The time-settings on the device are correct, i can reproduce it on my device and also on my pc in the sqlite browser...
A little bit more investigation on sqlight, because i think the sql-server is correct:
SQL:
select time('now')
returns on my computer not the local time but utc. With the modifier 'utc' it is definitly not UTC... I am confused. When i ask for
SQL:
Select (strftime('%s','now') - strftime('%s','2020-01-01 00:00:00')) as x
i got the correct value. Maybe the 'utc' modifier is wrong in this case... Can anyone verify this?

Matze
 
Upvote 0

MicroDrie

Well-Known Member
Licensed User
If
SQL:
select time('now')
returns on my computer not the local time but utc. With the modifier 'utc' it is definitly not UTC... I am confused. When i ask for
SQL:
Select (strftime('%s','now') - strftime('%s','2020-01-01 00:00:00')) as x
i got the correct value. Maybe the 'utc' modifier is wrong in this case... Can anyone verify this?

Matze
The result for SQLite is on my PC the UTC time.

If you install the Portable Apps application, you can use the also free "Database Browser for SQLite" and the also free "Database Browser" to test SQLite queries yourself on both your local SQLite Database and the remote SQL server Database in your own automation environment.
The free "Database Browser" program allows you to remotely connect to the database on your SQL server. With this approach you can make your own queries and test the result. I use this approach to first create and test my queries before I put them in my B4X program.

Update: The Portable Apps can be even run from a USB Stick and even don't modify your registry content on your PC. It also warns you for new software releases of the Portable Apps environment when it is started. Great simple program with a lot of free tools.
 
Last edited:
Upvote 0

TheRealMatze

Active Member
Licensed User
I have tested it here, i only want to make sure my diagnosis is correct. So, without the modifier the response is UTC (i hope this is correct)
 
Upvote 0
Top