sqlite时间不同步

[molaifeng]

接触sqlite没多久，不熟，今天查询出了问题，仔细看了下，把sql语句放在sqlite3里运行，发现，得到的时间如想要的不同步

echo date("Y-m-d H:i:s","1352304868");
//2012-11-08 05:41:53

而用sqlite的strftime函数

sqlite>  select strftime('%Y%m%d',1352304868,'unixepoch');
20121107

怎么相差了呢

[wwwwb]

SQLITE的日期是标准的格式

YYYY-MM-DD HH:MM:SS
检查你的格式是什么

2012-11-08 05:41:53：
select datetime('1352324513','unixepoch', 'localtime'),

[molaifeng]

啊，格式，怎么看

select datetime('1352324513','unixepoch', 'localtime');

这句可以转换成strftime么

[molaifeng]

哦，时间格式如下、

sqlite> select call_start_time from t_billing_cdr where cdr_filename='callrecord110812.cdr' limit 10;
1352303833
1352303931
1352303865
1352303770
1352304003
1352303902
1352303947
1352303994
1352303969
1352303996
sqlite> select strftime('%Y%m%d',call_start_time,'unixepoch') from t_billing_cdr where cdr_filename='callrecord110812.cdr' limit 10;
20121107
20121107
20121107
20121107
20121107
20121107
20121107
20121107
20121107
20121107

[WWWWA]

Select STRFTIME('%Y%m%d','1352324513','unixepoch', 'localtime');

[molaifeng]

好像还是有问题。

sqlite> select strftime('%Y%m%d',call_start_time,'unixepoch') as date,sum(fee) from t_billing_cdr group by strftime('%Y%m%d',call_start_time,'unixepoch');
20120331|0.0
20120401|0.0
20120404|0.0
20120406|0.0
20120411|0.0
20120412|0.0
20120413|0.0
20120416|0.0
20120417|0.0
20120418|0.0
20120419|0.0
20120420|0.0
20120427|0.0
20120519|0.0
20120531|0.0
20120604|0.0
20120605|0.0
20120608|0.0
20120609|0.0
20121107|3063.79999999964
20121109|1797.40000000023
20121110|15057.2000000197
20121111|18875.2000000336
20121112|848.400000000056
//上面是没有修改的

sqlite> select strftime('%Y%m%d',call_start_time,'unixepoch','localtime') as date,sum(fee) from t_billing_cdr group by strftime('%Y%m%d',call_start_time,'unixepoch');
20120401|0.0
20120401|0.0
20120405|0.0
20120406|0.0
20120412|0.0
20120412|0.0
20120413|0.0
20120417|0.0
20120417|0.0
20120418|0.0
20120420|0.0
20120420|0.0
20120427|0.0
20120519|0.0
20120531|0.0
20120604|0.0
20120605|0.0
20120608|0.0
20120609|0.0
20121108|3063.79999999964
20121110|1797.40000000023
20121111|15057.2000000197
20121112|18875.2000000336
20121112|848.400000000056
//这是用大大的

sql的逻辑就是统计当天的话费，可以再sqlite里就不能好好展现，纳闷了。用了函数后，时间都变了

[root@localhost sqlite]# ls -1 /var/asg/log/cache_cdr/
callrecord040112.cdr
callrecord040512.cdr
callrecord040612.cdr
callrecord040712.cdr
callrecord041112.cdr
callrecord041212.cdr
callrecord041312.cdr
callrecord041712.cdr
callrecord041812.cdr
callrecord042012.cdr
callrecord042112.cdr
callrecord042712.cdr
callrecord042812.cdr
callrecord050412.cdr
callrecord050812.cdr
callrecord050912.cdr
callrecord051912.cdr
callrecord052812.cdr
callrecord053012.cdr
callrecord053112.cdr
callrecord060112.cdr
callrecord060212.cdr
callrecord060412.cdr
callrecord060512.cdr
callrecord060812.cdr
callrecord060912.cdr
callrecord110712.cdr
callrecord110812.cdr
callrecord110912.cdr
callrecord111012.cdr
callrecord111112.cdr
callrecord111212.cdr

[ACMAIN_CHM]

你的操作系统时区是多少？ SELECT datetime(1352304868, 'unixepoch', 'localtime'); 返回应该是正确结果。 但如果你的时区不正确则不会返回东八区的时间。

[molaifeng]

谢谢大大了，已经解决了，原来上面少加了个'localtime',汗。

[root@localhost billadmin]# date -R
Fri, 16 Nov 2012 09:27:31 +0800

时区是东八区，我还是有点小疑惑，那个unixepoch不是计算从1970到现在的秒数么，为什么没有得到正确的值呢？