
Cari di Google plus nanya ke master MySQL (Mr.Furqon), dapet deh caranya.. dengan menggunakan fungsi DATE_ADD. Syntax-nya begini :
DATE_ADD(date,INTERVAL expr type)
Contohnya begini :
SELECT DATE_ADD(tgl_jtempo, INTERVAL 1 MONTH) AS tgl_jtempo2; > 2010-12-25 SELECT DATE_ADD(tgl_jtempo, INTERVAL -1 MONTH) AS tgl_jtempo2; > 2010-10-25
Simpel kan? Oya perintah parameter INTERVAL -nya juga bisa flexibel, kita juga bisa menambahkan hari, tahun, jam, menit, detik.
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; -> 1998-01-01 00:00:00 mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; -> 1998-01-01 mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; -> 1997-12-31 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); -> 1998-01-01 00:00:00 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY); -> 1998-01-01 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); -> 1998-01-01 00:01:00 mysql> SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND); -> 1997-12-30 22:58:59 mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); -> 1997-12-30 14:00:00 mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); -> 1997-12-02 mysql> SELECT EXTRACT(YEAR FROM "1999-07-02"); -> 1999 mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); -> 199907 mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); -> 20102
type Value | Expected expr format |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
MONTH | MONTHS |
YEAR | YEARS |
MINUTE_SECOND | "MINUTES:SECONDS" |
HOUR_MINUTE | "HOURS:MINUTES" |
DAY_HOUR | "DAYS_HOURS" |
YEAR_MONTH | "YEARS-MONTHS" |
HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
DAY_MINUTE | "DAYS HOURS:MINUTES" |
DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
Wah! Thanks banget yah! Very helpful! :) God bless you!
ReplyDeleteTQ...
ReplyDeleteLIKE THIS....