Menghitung selisih tanggal menggunakan fungsi DATEDIFF

MySQL: Menghitung selisih tanggal menggunakan fungsi DATEDIFF

sumber:https://gist.github.com/hidayat365/5899638

Raw

 datediff.sql

	Microsoft Windows [Version 6.1.7601]
	Copyright (c) 2009 Microsoft Corporation. All rights reserved.
	D:\xampp\mysql\bin>mysql -u root
	Welcome to the MySQL monitor. Commands end with ; or \g.
	Your MySQL connection id is 1
	Server version: 5.5.16 MySQL Community Server (GPL)
	Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
	Oracle is a registered trademark of Oracle Corporation and/or its
	affiliates. Other names may be trademarks of their respective
	owners.
	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
	mysql> use test;
	Database changed
	mysql> ----------------------------------------------------------------
	mysql> -- create table
	mysql> ----------------------------------------------------------------
	mysql> create table data_tanggal (
	-> id int auto_increment primary key,
	-> tanggal date
	-> ) ;
	Query OK, 0 rows affected (0.17 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- insert data ke table tanggal
	mysql> ----------------------------------------------------------------
	mysql> insert into data_tanggal (tanggal)
	-> values ('2013-01-01'), ('2013-02-01'), ('2013-03-01'), ('2013-04-01'), ('2013-07-01'), ('2013-08-01') ;
	Query OK, 6 rows affected (0.04 sec)
	Records: 6 Duplicates: 0 Warnings: 0
	mysql> ----------------------------------------------------------------
	mysql> -- coba lihat hasil insert-nya
	mysql> ----------------------------------------------------------------
	mysql> select * from data_tanggal ;
	+----+------------+
	| id | tanggal |
	+----+------------+
	| 1 | 2013-01-01 |
	| 2 | 2013-02-01 |
	| 3 | 2013-03-01 |
	| 4 | 2013-04-01 |
	| 5 | 2013-07-01 |
	| 6 | 2013-08-01 |
	+----+------------+
	6 rows in set (0.00 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- bandingkan tanggal hari ini dengan tanggal yang ada di dalam database
	mysql> ----------------------------------------------------------------
	mysql> select id, tanggal
	-> , current_date() as tgl_sekarang
	-> , datediff(current_date(), tanggal) as selisih
	-> from data_tanggal ;
	+----+------------+--------------+---------+
	| id | tanggal | tgl_sekarang | selisih |
	+----+------------+--------------+---------+
	| 1 | 2013-01-01 | 2013-07-01 | 181 |
	| 2 | 2013-02-01 | 2013-07-01 | 150 |
	| 3 | 2013-03-01 | 2013-07-01 | 122 |
	| 4 | 2013-04-01 | 2013-07-01 | 91 |
	| 5 | 2013-07-01 | 2013-07-01 | 0 |
	| 6 | 2013-08-01 | 2013-07-01 | -31 |
	+----+------------+--------------+---------+
	6 rows in set (0.00 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- sekarang dibalik,
	mysql> -- bandingkan tanggal yang ada di dalam database dengan tanggal hari ini
	mysql> ----------------------------------------------------------------
	mysql> select id, tanggal
	-> , current_date() as tgl_sekarang
	-> , datediff(tanggal, current_date()) as selisih
	-> from data_tanggal ;
	+----+------------+--------------+---------+
	| id | tanggal | tgl_sekarang | selisih |
	+----+------------+--------------+---------+
	| 1 | 2013-01-01 | 2013-07-01 | -181 |
	| 2 | 2013-02-01 | 2013-07-01 | -150 |
	| 3 | 2013-03-01 | 2013-07-01 | -122 |
	| 4 | 2013-04-01 | 2013-07-01 | -91 |
	| 5 | 2013-07-01 | 2013-07-01 | 0 |
	| 6 | 2013-08-01 | 2013-07-01 | 31 |
	+----+------------+--------------+---------+
	6 rows in set (0.00 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- sekarang coba bandingkan tanggal fixed 2013-05-20
	mysql> -- dengan tanggal yang ada di dalam database
	mysql> ----------------------------------------------------------------
	mysql> select id, tanggal
	-> , '2013-05-20' as tgl_sekarang
	-> , datediff('2013-5-20', tanggal) as selisih
	-> from data_tanggal ;
	+----+------------+--------------+---------+
	| id | tanggal | tgl_sekarang | selisih |
	+----+------------+--------------+---------+
	| 1 | 2013-01-01 | 2013-05-20 | 139 |
	| 2 | 2013-02-01 | 2013-05-20 | 108 |
	| 3 | 2013-03-01 | 2013-05-20 | 80 |
	| 4 | 2013-04-01 | 2013-05-20 | 49 |
	| 5 | 2013-07-01 | 2013-05-20 | -42 |
	| 6 | 2013-08-01 | 2013-05-20 | -73 |
	+----+------------+--------------+---------+
	6 rows in set (0.00 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- sekarang dibalik,
	mysql> -- bandingkan tanggal yang ada di dalam database
	mysql> -- dengan tanggal fixed 2013-05-20
	mysql> ----------------------------------------------------------------
	mysql> select id, tanggal
	-> , '2013-05-20' as tgl_sekarang
	-> , datediff(tanggal, '2013-5-20') as selisih
	-> from data_tanggal ;
	+----+------------+--------------+---------+
	| id | tanggal | tgl_sekarang | selisih |
	+----+------------+--------------+---------+
	| 1 | 2013-01-01 | 2013-05-20 | -139 |
	| 2 | 2013-02-01 | 2013-05-20 | -108 |
	| 3 | 2013-03-01 | 2013-05-20 | -80 |
	| 4 | 2013-04-01 | 2013-05-20 | -49 |
	| 5 | 2013-07-01 | 2013-05-20 | 42 |
	| 6 | 2013-08-01 | 2013-05-20 | 73 |
	+----+------------+--------------+---------+
	6 rows in set (0.00 sec)
	mysql> ----------------------------------------------------------------
	mysql> -- Semangan Belajar!!!
	mysql> ----------------------------------------------------------------
	mysql>