MySQL과 SQLite 날짜 함수 비교

(mysql) 시간값 더하고 빼기

 

DATE_ADD(NOW(), INTERVAL-90 DAY); 

DATE_ADD(NOW(), INTERVAL+90 DAY); 

DATE_ADD(NOW(), INTERVAL+2 MONTH); 

...

 

(sqlite) 시간값 더하고 빼기

sqlite> select datetime('2000-01-01 00:00:00', 'weekday 1');
2000-01-03 00:00:00
sqlite> select datetime('2000-01-01 00:00:00', '-1 year');
1999-01-01 00:00:00
sqlite> select datetime('2000-01-01 00:00:00', '+09:00:00');
2000-01-01 09:00:00
sqlite> select datetime(946684800,'unixepoch');
2000-01-01 00:00:00
...

 

 

DATETIME, TIMESTAMP 비교

DATETIME    : '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'

TIMESTAMP : '1970-01-01 00:00:00' ~ '2037-12-31 23:59:59'

 

 

(mysql) timestamp <-> Date

timestamp -> YYYY-MM-DD
   select from_unixtime(timestamp);

YYYY-MM-DD -> timestamp
   select unix_timestamp('YYYY-MM-DD');

 

(sqlite) timestamp <-> Date

timestamp -> YYYY-MM-DD
   select datetime(timestamp, 'unixepoch');
YYYY-MM-DD -> timestamp
   select strftime('%s', '2008-07-01 02:15:00');

 

sqlite> select julianday('2000-01-01 00:00:00');
  2451544.5
sqlite> select strftime('%Y-%m-%d %H:%M:%S','2000-01-01 12:34:56');
  2000-01-01 12:34:56

 

(sqlite) datetime 함수에서 로컬타임 얻기

기본은 GMT값을 얻어오므로 다음과 해주어야 한다.

sqlite> select datetime(timestamp, 'unixepoch', 'localtime');

sqlite> select datetime('now', 'localtime');

 

관련링크: http://blog.naver.com/kim109/50029009005

 

 

 

[출처] 딥블루 | 딥블루 (http://blog.naver.com/deepblue28?Redirect=Log&logNo=140113379071)