Dates in CSV are wrong (signed/unsigned issue?)

[NickSto]

• Operating system: Ubuntu 18.04
• Build version: v0.1.6-dirty

Detailed description

I tried reading my most recent backup and it works (thank you!) but the timestamps in the CSV output are all wrong.

I'm looking at the DATE_RECEIVED and DATE_SENT fields, and none correspond to the actual times I see in the app (which seem right, according to my memory).

Many of the timestamps have a dash in front of them, like they're negative, so I'm wondering if it's a simple issue of interpreting them as signed integers instead of unsigned ones?

Full relevant output

Here's some excerpts (phone numbers have been scrambled for privacy):

5,2,+18751131724,1,,1429838774,1429838774,0,1,-1,20,,0,,Yes!,,,-1,0,0,0,0,0
7,2,+18751131724,1,,1624285331,1624285331,0,1,-1,23,,0,,"Okay, now a second friend of mine signed up. Do I sense a pattern?",,,-1,0,0,0,0,0
8,4,+15636030277,1,,-2075312682,-2075312682,0,1,-1,20,,0,,"Jon HacDC is on Signal, say hey!",,,-1,0,0,0,0,0

and later in the output..

9,5,+97413301342,1,,1795218550,1795204960,31337,1,-1,10485780,1,0,,Yo,,GCM,-1,0,0,0,0,0

The actual timestamps of those messages, respectively, are:
2016-11-11 4:09:48PM EST
2016-11-13 10:10:35PM EST
2016-11-20 7:33:24PM EST
2017-01-04 2:42:02PM EST

[NickSto]

Update: Looking into it more, I haven't been able to munge the timestamps back with simple signed/unsigned transformations, so that might not be the issue.
But then again I'm no C expert, so hopefully I'm just not getting it right.

[hhgoten]

Got the same problem , tried every format in libreoffice, but can't get any Date/Time.
Nevertheless thanks to @xeals to give us the chance to extract it anyway.
So at least I got my written conversation-text and phonenumber in a file thats readable for me.
By the way -
this is only true for format CSV, xml does not work at all (other topic ??) , extract works like a charm.
Rsult of xml attached
backup_xml.txt

Best regards
hhgoten
10829 Berlin, Germany

[EvilRenegade]

Are you sure your export ran properly? In the CSV I got, I have 13-digit-unix-timestamps - i.e. millisecond-level unix time UTC.

I have yet to see one that doesn't match.

These came out of

signal-back v0.1.6-dirty
proto commit: d6610f0

[hhgoten]

@EvilRenegade
yes, export runs properly. as @NickSto describes, there are digits for time/date like 1459025111247 , but none of the formats of libreoffice can show/convert a date/time in a correct manner.
Formatting suggestions for the digits are welcome.
here is one line of the csv file:
(14,8,+491111111111,1,,1459025111247,1459025111247,0,1,0,23,,0,,Dran denken: Hier in BSA ist das Essen umsonst. Und auch gut.,,,-1,0,0,0,0,0)

[hhgoten]

Found
perl -e 'print scalar localtime(shift), "\n"' 1458320669840 gives part of good result
Sun May 5 12:57:20 48182
But I'm not good enough to use this in libreoffice calc :-((

[EvilRenegade]

Hold up. Those are more digits than in the original post. That looks proper.

As said: It should be a 13-digit unix epoch, millisecond level. Shave off the last three digits and run it through any unix time converter, and you should have your date.

e.g. you would take 1458320669840, erase the last three digits (1458320669) and get Fr 18. Mär 18:04:29 CET 2016. (date -d @1458320669)

If you're using a library that can handle millisecond-level timestamps for unix times, you can also use the stamps directly.

[hhgoten]

@EvilRenegade Thanks to you.
Then all is well to me.

In libreoffice I can use (with all digits )
=F2/86400000+25569
that gives
2016-03-26 20:45:11 for 1459025111247
an this is the correct date.

--

[EvilRenegade]

For anyone else who runs into issues, the converter at https://www.epochconverter.com/ accepts unix timestamps at millisecond level, i.e. the export timestamps work without modification with that.