C在SQLite中获取datetime列

C在SQLite中获取datetime列

本文介绍了如何使用Objective C在SQLite中获取datetime列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我有一个有4个字段的表: pk datetime value1 value2 pk (主键), value1 value2 是整数所以我使用:

  int value1 = sqlite3_column_int(statement,2); 
int value1 = sqlite3_column_int(statement,3);

但是我应该为 datetime 使用什么?

解决方案

在SQLite中,没有日期/时间列类型本身,所以最终代表日期为朱利安日期值(实数列)或字符串(文本列)。 SQLite也非常特别的如何在字符串中表示日期,yyyy-MM-dd HH:mm:ss(only)。



这些是我为使用来自Objective-C的SQLite日期。这些方法在NSDate的类别中实现。



请务必查看SQLite为使用朱利安日期而提供的功能。我发现这些是非常有用的()。导出NSDate的julianDay的函数包含在代码示例中。



看起来这个主题也在这里被覆盖。

  +(NSDate *)dateWithSQLiteRepresentation :( NSString *)myString; 
{
NSAssert3(myString,@%s:%d;%s;无效参数myString == nil,__FILE__,__LINE__,__PRETTY_FUNCTION__);

return [[self sqlLiteDateFormatter] dateFromString:myString];
}

+(NSDate *)dateWithSQLiteRepresentation :( NSString *)myString timeZone:(NSString *)myTimeZone;
{
NSString * dateWithTimezone = nil;
NSDate * result = nil;

NSAssert3(myString,@%s:%d;%s;无效参数myString == nil,__FILE__,__LINE__,__PRETTY_FUNCTION__);
NSAssert3(myTimeZone,@%s:%d;%s;无效参数myTimeZone == nil,__FILE__,__LINE__,__PRETTY_FUNCTION__);

dateWithTimezone = [[NSString alloc] initWithFormat:@%@%@,myString,myTimeZone];
result = [[self sqlLiteDateFormatterWithTimezone] dateFromString:dateWithTimezone];
[dateWithTimezone release];

返回结果;
}

+(NSString *)sqlLiteDateFormat;
{
return @yyyy-MM-dd HH:mm:ss;
}

+(NSString *)sqlLiteDateFormatWithTimeZone;
{
static NSString * result = nil;

if(!result){
result = [[NSString alloc] initWithFormat:@%@ zzz,[self sqlLiteDateFormat]];
}

返回结果;
}

+(NSDateFormatter *)sqlLiteDateFormatter;
{
static NSDateFormatter * _result = nil;

if(!_result){
_result = [[NSDateFormatter alloc] init];
[_result setDateFormat:[self sqlLiteDateFormat]];
}

return _result;
}

+(NSDateFormatter *)sqlLiteDateFormatterWithTimezone;
{
static NSDateFormatter * _result = nil;

if(!_result){
_result = [[NSDateFormatter alloc] init];
[_result setDateFormat:[self sqlLiteDateFormatWithTimeZone]];
}

return _result;
}


- (NSString *)sqlLiteDateRepresentation;
{
NSString * result = nil;

result = [[NSDate sqlLiteDateFormatter] stringFromDate:self];

返回结果;
}

- (NSTimeInterval)unixTime;
{
NSTimeInterval result = [self timeIntervalSince1970];

返回结果;
}

#define SECONDS_PER_DAY 86400
#define JULIAN_DAY_OF_ZERO_UNIX_TIME 2440587.5
- (NSTimeInterval)julianDay;
{
return [self unixTime] / SECONDS_PER_DAY + JULIAN_DAY_OF_ZERO_UNIX_TIME;
}

+(NSDate *)dateWithJulianDay :( NSTimeInterval)myTimeInterval
{
NSDate * result = [self dateWithTimeIntervalSince1970:(myTimeInterval - JULIAN_DAY_OF_ZERO_UNIX_TIME)* SECONDS_PER_DAY];

返回结果;
}


How do you get a datetime column in SQLite with Objective C?

I have a table with 4 fields: pk, datetime, value1 and value2. pk (primary key), value1 and value2 are integers so I am using:

   int value1 = sqlite3_column_int(statement, 2);
   int value1 = sqlite3_column_int(statement, 3);

But what should I use for datetime?

解决方案

In SQLite, there is no date/time column type per se, so one ends up representing dates either as Julian date values (real columns) or in strings (text columns). SQLite is also very particular in how dates are represented in strings, yyyy-MM-dd HH:mm:ss (only).

These are some methods that I wrote for working with SQLite dates from Objective-C. These methods are implemented in a category on NSDate.

Be sure to check out the functionality that SQLite offers for working with Julian dates. I have found these to be quite useful (http://www.sqlite.org/lang_datefunc.html). A function for deriving an NSDate's julianDay is included in the code example.

It looks like this subject was also covered here.Persisting Dates to SQLite3 in an iPhone Application

+ (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString;
{
    NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);

    return [[self sqlLiteDateFormatter] dateFromString: myString];
}

+ (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString timeZone: (NSString *) myTimeZone;
{
    NSString * dateWithTimezone = nil;
    NSDate * result = nil;

    NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);
    NSAssert3(myTimeZone, @"%s: %d; %s; Invalid argument. myTimeZone == nil",  __FILE__, __LINE__, __PRETTY_FUNCTION__);

    dateWithTimezone = [[NSString alloc] initWithFormat: @"%@ %@", myString, myTimeZone];
    result = [[self sqlLiteDateFormatterWithTimezone] dateFromString: dateWithTimezone];
    [dateWithTimezone release];

    return result;
}

+ (NSString *) sqlLiteDateFormat;
{
    return @"yyyy-MM-dd HH:mm:ss";
}

+ (NSString *) sqlLiteDateFormatWithTimeZone;
{
    static NSString * result = nil;

    if (!result) {
        result = [[NSString alloc] initWithFormat: @"%@ zzz", [self sqlLiteDateFormat]];
    }

    return result;
}

+ (NSDateFormatter *) sqlLiteDateFormatter;
{
    static NSDateFormatter * _result = nil;

    if (!_result) {
        _result = [[NSDateFormatter alloc] init];
        [_result setDateFormat: [self sqlLiteDateFormat]];
    }

    return _result;
}

+ (NSDateFormatter *) sqlLiteDateFormatterWithTimezone;
{
    static NSDateFormatter * _result = nil;

    if (!_result) {
        _result = [[NSDateFormatter alloc] init];
        [_result setDateFormat: [self sqlLiteDateFormatWithTimeZone]];
    }

    return _result;
}


- (NSString *) sqlLiteDateRepresentation;
{
    NSString * result = nil;

    result = [[NSDate sqlLiteDateFormatter] stringFromDate: self];

    return result;
}

- (NSTimeInterval) unixTime;
{
    NSTimeInterval result = [self timeIntervalSince1970];

    return result;
}

#define SECONDS_PER_DAY 86400
#define JULIAN_DAY_OF_ZERO_UNIX_TIME 2440587.5
- (NSTimeInterval) julianDay;
{
    return [self unixTime]/SECONDS_PER_DAY + JULIAN_DAY_OF_ZERO_UNIX_TIME;
}

+ (NSDate *) dateWithJulianDay: (NSTimeInterval) myTimeInterval
{
    NSDate *result = [self dateWithTimeIntervalSince1970: (myTimeInterval - JULIAN_DAY_OF_ZERO_UNIX_TIME) * SECONDS_PER_DAY];

    return result;
}

这篇关于如何使用Objective C在SQLite中获取datetime列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-02 19:02