Android Question Timestamp-problem

[TheRealMatze]

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

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

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]

Are you sure that '01.01.2020' is the right format? Should that not be '01-01-2020' to prevent a wrong result?

 

[emexes]

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

 

[Chris2]

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?

Issue retrieving SQL Server DateTime2 column at change of clocks (GMT to BST)

I think I've found an issue in the MS JDBC driver similar but slightly different to that discussed previously in relation to jRDC2 here. This time I came across a problem when querying an MS SQL Server database DateTime2 column directly with an SQL object (via jConnectionPool). I think I've...

www.b4x.com

jRDC2, retrieve SQL Server DateTime2 as UTC

I have UTC date/times stored in a SQL Server database datetime2(0) column. They are written as a string in the format 'yyyy-MM-dd HH:mm:ss'. I'm in the UK so until last Sunday our clock time was UTC/GMT, but it's now BST (UTC/GMT + 01:00) Since the local clock change, when I query the database...

www.b4x.com

 

[TheRealMatze]

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:

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

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

 

[MicroDrie]

If

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

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.

 

[TheRealMatze]

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)