SQLite存储，检索和比较DATETIME字段 [英] SQLite storing, retrieving and comparing a DATETIME field

问题描述

存储日期： >

此文档告诉我使用dateformat在下面指定，但似乎不正确。我尝试使用 yyyy-MM-dd hh：mm：ss 而不成功。

  NSDate * today = [NSDate date]; 
 NSDateFormatter * dateFormat = [[NSDateFormatter alloc] init]; 
 [dateFormat setDateFormat：@YYYY-MM-DD HH：MM：SS]; 
 NSString * dateString = [dateFormat stringFromDate：today]; 
 
 NSString * query = [NSString stringWithFormat：@INSERT INTO user（edited）VALUES（\％@ \），dateString]; 
  

比较日期

我正在使用这个时间戳，我转换为datetime字符串

  long stamp = [sessie integerForKey： @邮票]; 
 
 NSDateFormatter * formatter = [[NSDateFormatter alloc] init]; 
 [formatter setDateFormat：@YYYY-MM-DD HH：MM：SS]; 
 NSString * dateString = [formatter stringFromDate：[NSDate dateWithTimeIntervalSince1970：stamp]]; 
 
 sqlite3_stmt * result = [db selectQuery：[NSString stringWithFormat：@SELECT * FROM user WHERE edited>'％@'，dateString]]; 
  

时间戳只是使用 [[NSDate date] timeIntervalSince1970] 。问题是查询不会给出正确的结果，我甚至不知道日期是否正确存储在第一位。任何帮助将不胜感激！

解决方案

几个意见：

1. 对于您的日期字符串，您绝对不想使用 YYYY-MM-DD HH：MM：SS 。这不会生成有效的日期字符串。使用 yyyy-MM-dd hh：mm：ss 更接近，但不太正确，因为你会使用12小时 hh ）。改用 yyyy-MM-dd HH：mm：ss




2. 尽管如此，这种日期格式并不捕捉时区，所以如果在SQLite数据库中存储日期字符串，则应使用UTC（GMT）作为在SQLite 日期和时间功能文档中讨论。

     NSDateFormatter * formatter = [[NSDateFormatter alloc] init]; 
    [formatter setDateFormat：@yyyy-MM-dd HH：mm：ss]; 
    formatter.timeZone = [NSTimeZone timeZoneWithName：@UTC]; 
    formatter.locale = [[NSLocale alloc] initWithLocaleIdentifier：@en_US_POSIX]; 
    NSString * dateString = [formatter stringFromDate：today]; 
     

   如上所示，您可能还想指定 locale ，以便日期的格式不会根据设备的本地化设置而改变。




3. 请注意，您可以考虑使用 timeIntervalSince1970 将日期存储在数据库中的 REAL 值（而不是 TEXT ）。这可以更有效率并自动解决UTC问题。

I am really stuck trying to compare dates in SQLite queries in Objective C. Here's what I'm doing:

Storing the date:

This document tells me to use the dateformat specified below, but it doesn't seem right. I tried using yyyy-MM-dd hh:mm:ss without success too though.

NSDate *today = [NSDate date];
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"YYYY-MM-DD HH:MM:SS"];
NSString *dateString=[dateFormat stringFromDate:today];

NSString *query = [NSString stringWithFormat: @"INSERT INTO user (edited) VALUES (\"%@\")", dateString];

Comparing the date

I am using a timestamp for this, which I convert to a datetime string

long stamp = [sessie integerForKey:@"stamp"];

NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
[formatter setDateFormat:@"YYYY-MM-DD HH:MM:SS"];
NSString *dateString = [formatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:stamp]];

sqlite3_stmt *result = [db selectQuery:[NSString stringWithFormat:@"SELECT * FROM user WHERE edited > '%@'", dateString]];

The timestamp is simply generated using [[NSDate date] timeIntervalSince1970]. The problem is that the query won't give the correct results, and I don't even know if the date is stored correctly in the first place. Any help would be greatly appreciated!

解决方案

A couple of observations:

1. For your date string, you do definitely do not want to use YYYY-MM-DD HH:MM:SS. That will not generate a valid date string. Using yyyy-MM-dd hh:mm:ss is much closer, but not quite right, either (since you'll use 12-hour hh). Use yyyy-MM-dd HH:mm:ss instead.

2. This date format, though, does not capture time zone, so, if you store date strings in your SQLite database, you should use UTC (GMT) as discussed in the SQLite Date And Time Functions documentation.

   NSDateFormatter * formatter = [[NSDateFormatter alloc] init];
   [formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
   formatter.timeZone = [NSTimeZone timeZoneWithName:@"UTC"];
   formatter.locale = [[NSLocale alloc] initWithLocaleIdentifier:@"en_US_POSIX"];
   NSString *dateString = [formatter stringFromDate:today];
   

   As shown above, you probably want to also specify the locale so that the format of the dates will not change depending upon the localization settings of the device.

3. Note that you might consider using timeIntervalSince1970 to store the date as a REAL value in your database (as opposed to a TEXT). This can be a little more efficient and automatically addresses the UTC issue.

这篇关于SQLite存储，检索和比较DATETIME字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！