Sunday, April 17, 2011

MySQL : Menambah bulan / hari / tahun dengan fungsi DATE_ADD

Hari ini lagi bikin script PHP ketemu masalah ketika ingin menambahkan bulan pada field bertype DATE. Misalnya jika ada data 2010-12-01 maka saya harus bisa menambahkan bulannya tanpa menggangu tanggal-nya dan tahun akan otomatis berubah jika penambahan bulan tsb melebihi Desember.

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"

2 comments:

  1. Wah! Thanks banget yah! Very helpful! :) God bless you!

    ReplyDelete
  2. TQ...
    LIKE THIS....

    ReplyDelete

Leave your comments with attitude please. This blog does DOFOLLOW :-)