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>