Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bad date in PostgreSQL column ReceivingDateTime in 1.40.0 #447

Open
pixall opened this issue Feb 10, 2019 · 3 comments

Comments

Projects
None yet
2 participants
@pixall
Copy link

commented Feb 10, 2019

After upgrading to 1.40.0, timestamps in inbox table, column ReceivingDateTime, went wrong. I have found that this is caused by patch #305 . At least for me, this patch is not working OK.

I have reviewed sources and I have found:

Originaly, there was gmtime() function used to convert timestamp to GMT, and then it was formatted for Postgres with GMT suffix.

Currently, there is locattime() used, which returns time in local format. This time is written to Postgresql with "GMT" suffix too, which is bad - we are writing local time, not GMT time, so adding GMT do the time is creating invalid timestamp in database for every timezone except GMT.

It is necessary to remove "GMT" from SQL statement (line 296). If no timezone will be entered, Postgreql should treat it like local time, and add local time zone, which is OK. Or eventually, correct timezone can be added to SQL command (line 296).


Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

@melones

This comment has been minimized.

Copy link

commented Apr 30, 2019

Hello @pixall,

Removing "GMT" from SQL statement (line 296) doesn't resolve the problem. At least in my tests.
I belive that problem is that function locattime() doesn't return correct time (converted to local timezone) when incoming SMS contains different timezone than local timezone.

I attach log example that shows that.

SMS received:

Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Getting message from cache
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Number Length=7
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Number type 91 (1 0 0 1|0 0 0 1)
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: International number
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Len 6
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: SMS center number : "+48707990063"
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: SMS type: Deliver
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Number Length=15
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Number type 91 (1 0 0 1|0 0 0 1)
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: International number
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Len 8
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Remote number : "+481234567898911"
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: SMS PID: 0x00
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: SMS DCS: 0x00
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: SMS class: -1
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Decoding date & time: Fri Apr 26 07:02:17 2019 -0700
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: 7 bit SMS, length 81
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: $QR:COMM=3,+48111111111,,070E.TMGS,internet,internet,1,164.132.189.113,7002,180,0
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: gammu: Leaving GSM_GetNextSMS

Insert into inbox table:
Fri 2019/04/26 16:02:20 gammu-smsd[1705]: Execute SQL: INSERT INTO inbox ("ReceivingDateTime", "Text", "SenderNumber", "Coding", "SMSCNumber", "UDH", "Class", "TextDecoded", "RecipientID", "Status") VALUES ('2019-04-26 07:02:17 GMT', '0024XXXXXXX', '+467191202376986', 'Default_No_Compression', '+481234567898911', '', -1, '$QR:COMM=3,+48111111111,,070E.TMGS,internet,internet,1,164.132.189.113,7002,180,0', 'phone1', 0)

If you compare received date/time from SMS: Decoding date & time: Fri Apr 26 07:02:17 2019 -0700
and receiving date/time inserted into inbox: 2019-04-26 07:02:17 GMT [timezone_problem.txt](https://github.com/gammu/gammu/files/3131174/timezone_problem.txt) you can see that inserted date/time wasn't converted to correct local time.

timezone_problem.txt

@pixall

This comment has been minimized.

Copy link
Author

commented May 1, 2019

@melones You're right. I think passing timezone data from message to SQL command should fix it finally..

@melones

This comment has been minimized.

Copy link

commented May 7, 2019

@gnidorah, @kstuart would you like to fix it with bounty gratification?

@kstuart kstuart referenced a pull request that will close this issue May 17, 2019

Open

fix timestamp conversions #472

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.