CODEWITHSUNDEEP
phpmysqliosobjective-cnsdate
Pierre
Pierre

Reputation: 11663

NSDate to MySQL date

I would like to save NSDate to a MySQL field. Which type is the best? DATE or TIMESTAMP? And how to format my NSDate to send it (to a PHP script) and save it in my field?

Upvotes: 2

Views: 4798

Answers (5)

malhal
malhal

Reputation: 30719

New versions of MySQL supports fractional seconds so here is my solution for that:

@implementation NSDate (MySQL)

//used to send a utc date to the server.
-(NSString*)yourprefix_MySQLString{
    static NSDateFormatter *df = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        df = [[NSDateFormatter alloc] init];
        // [df setDateFormat:@"yyyy-MM-dd HH:mm:ss.SSSSSS"]; can't use this because three S's
        // is the maximum and rest are 0, e.g. 1415986217.544384 with six S's becomes .544000
        // so missing some of the fraction.
        [df setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
        [df setTimeZone:[NSTimeZone timeZoneWithAbbreviation:@"UTC"]];
    });
    double d = [self timeIntervalSince1970];
    d = d - floor(d); // extract fraction
    d*=1000000; // convert to micros
    d = round(d);
    return [NSString stringWithFormat:@"%@.%06d", [df stringFromDate:self], (int)d];
}

@end

Example:

NSDate* d = [NSDate date];
NSLog(@"%@", d);
NSLog(@"%f", [d timeIntervalSince1970]);
NSLog(@"%@", [d yourprefix_MySQLString]);

Output:

2014-11-14 17:47:33 +0000
1415987253.326594
2014-11-14 17:47:33.326594

Notice on line 2 and line 3 the fraction matches exactly, that's what I wanted.

As always put your own prefix to the category to prevent collisions with other developers categories.

Upvotes: 0

benzado
benzado

Reputation: 84338

You asked two questions.

Regarding MySQL DATETIME vs TIMESTAMP, see Should I use field 'datetime' or 'timestamp'?

Regarding getting from NSDate to MySQL via PHP...

NSDate is an object representing a point in time. To send it over a network, you need to serialize it into some format (that is, turn it into a string). The easiest thing to do is put it in the format yyyy-MM-dd HH:mm:ss, because that is what MySQL will want.

Use NSDateFormatter to convert the NSDate object into a string of that format. Make sure to set the formatter time zone to UTC, so you will get consistent results regardless of the time zone of the device/computer your NSDate was computed on. Then send it over the network to your PHP script.

In the PHP script, you can just take the value directly and save it to a DATETIME column.

Bonus: make sure you use prepared statements (the PHP PDO library, which comes with PHP will allow this), otherwise you will be at risk for SQL injection.

Upvotes: 4

Jakob Egger
Jakob Egger

Reputation: 12041

First create a date formatter:

NSDateFormatter *gmtDateFormatter = [[NSDateFormatter alloc] init];
gmtDateFormatter.timeZone = [NSTimeZone timeZoneForSecondsFromGMT:0];
gmtDateFormatter.dateFormat = @"yyyy-MM-dd HH:mm:ss";

To convert from NSDate to a MySQL compatible date string:

NSString *dateString = [gmtDateFormatter stringFromDate:aDate];

To convert back:

NSDate *date = [gmtDateFormatter dateFromString:aString];

Upvotes: 9

John Conde
John Conde

Reputation: 219884

I would transform it into a datetime format (YYYY-MM-DD HH:MM:SS). That way you don't have Unix epoch issues or y38k issues. Plus PHP can work with that format very easily.

Upvotes: 0

Evol Gate
Evol Gate

Reputation: 2257

I did the same thing in my app once. I used to store NSDate as string. But I used SQLite in my project. But storing NSDate as string worked quite well for me. At the time of fetching data convert the string to NSDate again. This approach has few benefits too as you always fetch data in string format, so its pretty easy to handle things in the project.

Upvotes: 0

Related Questions