CODEWITHSUNDEEP
mysqliosobjective-cios7timestamp
Pavan
Pavan

Reputation: 18528

Convert from an unknown timestamp to a readable timestamp

I have the below column containing timestamps retrieved from an .sqlite file,

what format are these timestamps in and how can one convert from them to a readable timestamp?

Actually if I knew the format of the timestamp shown I could figure out the rest.

I have tried pasting this in timestamp conversion tools online but the converter cannot identify the particular format. I intially thought that the timestamp was in the format of YYYYMMDDHHMMSS.uuuuuu but the length of digits don't correspond to length of the format so I'm scratching my head on this one

Any help?

Update 1

These timestamps correspond to messages so its obvious that there must be atleast the minutes and seconds retained in that timestamp.

Update 2

These timesstamps came from a messaging application which I retrieved from a .sqlite file

Update 3

Based on the additional columns provided such as Name and message, I was able to verify that the timestamp correlates to the message timestamp inside the app to the 11/28/2012 10:48am I was not able to verify the seconds.

Upvotes: 0

Views: 442

Answers (2)

Midhun MP
Midhun MP

Reputation: 107211

You can use + (instancetype)dateWithTimeIntervalSince1970:(NSTimeInterval)seconds if the second is since 1970:

NSLog(@"%@",[NSDate dateWithTimeIntervalSince1970:375792535.395])

Output:

1981-11-28 10:48:55 +0000

It seems that your second is since 2001 (As mentioned by @vikingosegundo in his answer), so you can use 

+(instancetype)dateWithTimeIntervalSinceReferenceDate:(NSTimeInterval)seconds

Example:

NSLog(@"%@", [NSDate dateWithTimeIntervalSinceReferenceDate:375792535.395]);

Output:

2012-11-28 11:24:51 +0000

And then you can use NSDateFormatter for formatting the date in desired format.

Upvotes: 1

vikingosegundo
vikingosegundo

Reputation: 52227

these are seconds passed since a certain time. Usually 1st of January 1970 0:00 is the first second. but in this case it would be a date that makes not much sense, as the first timestamp would be Sat, 28 Nov 1981 10:48:55 GMT. Tell us what the app shows for the fist timestamp and it will be easy to calculate the reference date.

possibly 1st January 2001, as it is another often use reference date. i.e. on Mac OS X and iOS.

NSDate *date  = [NSDate dateWithTimeIntervalSinceReferenceDate:timestampFromDB];

I wrote a test programm.

#import <Foundation/Foundation.h>

int main(int argc, const char * argv[])
{
    @autoreleasepool {
        NSTimeInterval intervals[] = {375792535.395, 375792980.868017};
        for (int i = 0; i < sizeof(intervals)/sizeof(intervals[0]); ++i) {
            NSTimeInterval interval = intervals[i];
            NSDate *date = [NSDate dateWithTimeIntervalSinceReferenceDate:interval];
            NSLog(@"%@", date);
        }
    }
    return 0;
}

logs:

2012-11-28 10:48:55 +0000
2012-11-28 10:56:20 +0000

for the first two timestamps. please check, if this are the dates shown in the app.

Conclusion:

The database holds double values to represent the seconds that passed since 1.1.2001 0:00 GMT, what is called the "Reference Date" in the Mac (OS X) world. The column does not hold a string with a certain format.

Upvotes: 3

Related Questions