如何使用 Objective C 在 SQLite 中获取日期时间列 [英] How get a datetime column in SQLite with Objective C

问题描述

如何使用 Objective C 在 SQLite 中获取日期时间列?

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

我有一个包含 4 个字段的表:pk、datetime、value1 和 value2.pk(主键)、value1 和 value2 是整数，所以我使用:

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);

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

But what should I use for datetime?

推荐答案

在 SQLite 中，本身没有日期/时间列类型，因此最终将日期表示为 Julian 日期值(实列)或字符串(文本列).SQLite 也很讲究日期在字符串中的表示方式，yyyy-MM-dd HH:mm:ss(仅限).

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).

这些是我为处理来自 Objective-C 的 SQLite 日期而编写的一些方法.这些方法在 NSDate 上的一个类别中实现.

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

请务必查看 SQLite 提供的用于处理儒略日期的功能.我发现这些非常有用(http://www.sqlite.org/lang_datefunc.html).代码示例中包含用于导出 NSDate 的 julianDay 的函数.

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.

这里似乎也涵盖了这个主题.在 iPhone 应用程序中将日期保存到 SQLite3

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 中获取日期时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！