Monday, January 31, 2011

MySQL Date Time

Date dan Time adalah sebuah "mahluk" yang tidak dapat dipisahkan dalam kehidupan kita. Katanya bila bisa me-manage waktu dengan baik maka akan lebih dekat dengan kesuksesan.




Dalam pemrograman komputer persoalan Date dan Time di banyak aplikasi selalu terjadi, dari menampilkan format tanggal hingga operasi-operasi yang melibatkan Date & Time.

Dari beragam fitur yang dimiliki Database MySQL, diantaranya adalah fitur untuk Untuk melakukan manipulasi Time dan Date yaitu DATEDIFF, TIMEDIFF, TIME_TO_SEC. Fungsi-fungsi tersebut  dapat mempermudah dan mempercepat dalam mengelolah hal-hal yang berhubungan dengan Time & Date.

Berikut ini adalah beberapa kegunaan dari memanfaatkan fitur Time & Date yang dimiliki Database MySQL.

Database MySQL yang saya gunakan versi 5.1 dengan Operation System Ubuntu 11.04 - the Natty Narwhal


A. Menghitung Keterlambatan Hari 

Menghitung keterlambatan hari Biasanya di implemetasikan di persoalan-persoalan yang berhubungan dengan peminjaman, seperti perpustakaan dan rental film.

Misalkan saya memiliki data peminjam sebagai berikut. Kemudian saya ingin menghitung keterlambatan pengembalian buku.



Berikut ini adalah cara untuk menghitung keterlambatan hari
SELECT id_pegawai, id_buku, DATEDIFF(NOW(),tgl_batas_pinjam) AS keterlambatan FROM peminjaman;

Penjelasan
  • DATEDIFF = Fungsi untuk menghitung selisih tanggal


B. Menghitung Usia

Misalkan saya memiliki data karyawan sebagai berikut. Kemudian saya ingin menghitung umur setiap karyawan.



Berikut ini adalah cara untuk menghitung usia.
SELECT nama_depan, tgl_lahir, ((YEAR(NOW()) - YEAR(tgl_lahir))) - (RIGHT(NOW(),5) <  RIGHT(DATE(tgl_lahir),5)) AS usia  FROM pegawai;


Penjelasan Fungsi MySQL
  • NOW = Fungsi untuk menampilkan tanggal sekarang
  • YEAR = Fungsi untuk mengambil tahun dari sebuah tanggal
  • RIGTH = Fungsi untuk mengambil karakter dari sebelah kanan


C. Menghitung Jam Kerja 

Menghitung jam kerja dapat di manfaatkan untuk menghitung jam kerja karyawan di hari yang berjalan atau untuk menghitung akumulasi jam kerja yang telah berlalu.  

Misalakan saya memiliki table absensi pegawai seperti dibawah ini. Kemudian saya memerlukan data tentang jumlah jam kerja berjalan pegawai dan jumlah total jam kerja pegawai.


Menampilkan Jumlah Jam Kerja dihari berjalan
SELECT id_pegawai, (TIMEDIFF(jam_keluar,jam_masuk)) as jam_kerja FROM absen;



Menampilkan Jumlah Total Jam Kerja karyawan
SELECT id_pegawai, SEC_TO_TIME(SUM((TIME_TO_SEC(TIMEDIFF(jam_keluar,jam_masuk))))) as jam_kerja FROM absen GROUP BY id_pegawai;


Penjelasan Fungsi MySQL
  • TIMEDIFF = Fungsi untuk membandingkan jam
  • TIME_TO_SEC = Fungsi untuk merubah jam menjadi detik
  • SUM = FUNGSI untuk menjumlahkan
  • SEC_TO_TIME = Fungsi untuk merubah detik menjadi jam

70 comments:

  1. thanks berat gan.... saya nyari script total jam kerja.... akhirnya ketemu juga...

    ReplyDelete
  2. gan mau tanya lagi donk.

    mengenai fungsi enable disable.

    kalau kasusnya seperti ini gimana :

    saya punya web absensi, dan ingin menggunakan fungsi disable berdasarkan hari.

    misal : jika sekarang tanggal 25 agustus 2011 saya tidak mengisi form absen, maka besok tanggal 26 agustus yang tanggal 25 agustus tidak bisa di isi pada tanggal 26 agustus (disable). scriptnya kira2 gimana yah gan ?

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Biasanya dilakukan validasi tanggal dilevel aplikasi sebelum di input ke database.

    Berikut ini contoh validasi tanggal menggunakan store procedure mysql.

    1. Membuat store procedure insert tabel absensi untuk berikut validasi tanggalnya.

    CREATE PROCEDURE `sp_absen_add`(nik char(10),tanggal char(10),status varchar(255))

    BEGIN
    declare tglSkrg varchar(10);
    declare status int;

    select date_format(now(),'%Y-%m-%d') into tglSkrg;

    if (tanggal = tglSkrg) then
    /*letakan query untuk insert absen*/
    set status = 1;
    end if;

    END

    2. Panggil store procedure tersebut seperti contoh dibawah ini.

    sp_absen_add('200704001','2011-08-26','ADD')

    ReplyDelete
  7. rada sedikit bingung juga yah... berikut screen shoot gambarnya gan..

    http://www.diskusiweb.com/discussion/41021/fungsi-tanggal-disable

    saya sih inginya seperti kasus ini.. itu saya juga yang post..

    jadi pada saat tanggal di pilih karena sudah lewat kolom isian menjadi disable.

    ato boleh gak gan buatkan web sederhana nya saja seperti contoh di atas.. biar saya bisa pelajari... saya udah bookmark nih web mantap..hehehe... tolong yah gan...

    ReplyDelete
  8. oh klw yang seperti itu persoalannya pada validasi tanggal di level javascript.

    Tapi aku mau kasih solusi yang cukup simple untuk masalah mu.. apabila menggunakan php gunakan validasi tanggal seperti di bawah ini.

    $exp_date = "2006-01-16";
    $todays_date = date("Y-m-d");

    $today = strtotime($todays_date);
    $expiration_date = strtotime($exp_date);

    if ($expiration_date > $today) {
    $valid = "yes";
    } else {
    $valid = "no";
    }

    sumber : http://www.highlystructured.com/comparing_dates_php.html

    ReplyDelete
  9. gan mu tanya lagi kalau menjumlahkan jam perhari gimana yah ?

    kalau kemarin kan saya jumlahkanya per keseluruhan.

    nah kalau perhari scriptnya gimana yah gan ?

    misal hari ini saya kerja 8 jam, kemarin saya kerja 8 jam. itu gimana yah gan cara menampilkanya...

    ReplyDelete
  10. SQL nya begini.

    SELECT id_pegawai,tanggal, SEC_TO_TIME(SUM((TIME_TO_SEC(TIMEDIFF(jam_keluar,jam_masuk))))) as jam_kerja FROM absen group by id_pegawai,tanggal

    ReplyDelete
  11. hatur nuhun + thankyou gan.... akhirnya bisa juga...

    nanti saya tanya tanya lagi gan..hehe.. punya guru baru..

    ReplyDelete
  12. bang minta pencerahanya ....
    kalo ingin menampilkan ulang tahun kita dari tanggal kelahiran kita secara detail gimana ...
    bukan cuma umurnya saja ..tapi juga dari hari bulan n tahun tepat kita Ultah gmn baaang ..
    Please help mee ...

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. @anonim. coba ini untuk menampilkan ultah yang isinya hari, tgl, bulan dan tahun.

    select date_format('2011-10-23','%W %d %M %Y') as tgl_lahir;
    +-----------------------------------------+
    | tgl_lahir |
    +-----------------------------------------+
    | Sunday 23 October 2011 |
    +-----------------------------------------+

    Kalau diimplementasikan dalam sebuah tabel yang berisikan profile member akan seperti ini.

    select if(date_format(now(),'%Y-%m-%d') = date_format(tgl_lahir,'%Y-%m-%d'), date_format('2011-10-23','%W %d %M %Y'),null);

    ReplyDelete
  16. Gan , kalo mw nampilin data karyawan yang bekerja sudah lebih dari 1 tahun gmna ya ?

    ReplyDelete
  17. @anonim cara menampilkan karyawan yang lebih dari 1 tahun bisa seperti ini

    select * from karyawan where date_format(tgl_masuk,'%j') > 365

    ReplyDelete
  18. gan mw tnya?? klo mengitung umur berdasarkan jumlah bulan gmn scriptx??

    ReplyDelete
  19. @anonim.. caranya begini

    SELECT FLOOR(30/12);


    Ket:

    30 = Jumlah Bulan
    12 = Jumlah bulan dalam satu tahun
    Floor = Function untuk membulatkan bilangan

    ReplyDelete
  20. mas, saya mau tanya,
    kalo update otomatis fild database berdasarkn tanggal di bios laptop kita bisa nggak?

    ReplyDelete
  21. mas saya ikutan tanya.
    kalau mau hitung usia.
    misal:
    22 tahun 9bulan 20hari

    ReplyDelete
  22. mas gmn caranya membandingkan jam??
    misal:
    jam1 : 10:00:00
    jam2 : 12:00:00
    kedua jam tsb ketika dibandingkan hasilnya jam1 lebih dulu dulu dibanding jam2 ato sebaliknya...
    makasi kk..

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Apakah maksudnya membandingkan dua buah waktu untuk mendapatka selisihnya ?, berikut ini adalah contohnya.

      mysql> SELECT TIMEDIFF('10:00:00','12:00:00');
      +---------------------------------+
      | TIMEDIFF('10:00:00','12:00:00') |
      +---------------------------------+
      | -02:00:00 |
      +---------------------------------+
      1 row in set (0.09 sec)

      mysql> SELECT TIMEDIFF('12:00:00','10:00:00');
      +---------------------------------+
      | TIMEDIFF('12:00:00','10:00:00') |
      +---------------------------------+
      | 02:00:00 |
      +---------------------------------+
      1 row in set (0.00 sec)

      Delete
  23. gan cara menghitung keterlambatan hari pada tanggal dengan format varchar gimana??cz punya ane tanggalnya format varchar

    ReplyDelete
  24. mas klo saya ingin tampilkan seluruh tanggal (misal : dari tgl 2012-11-01 s/d 2012-11-30) semua tanggal tampil, tanpa harus yg ada di database bgmn yah?

    ReplyDelete
    Replies
    1. Maksud "tanpa harus yg ada di database bgmn yah?" gimana ya ?

      Delete
  25. mas misalkan mau menampilkan data yang ada khusus untuk tanggal hari ini saja bagaimana mas???

    ReplyDelete
    Replies
    1. Caranya adalah :

      select * from where date_format(tanggal,'%d-m%-%Y') = date_format(now(),'%d-m%-%Y');

      Delete
  26. saya pake MySql 5.1.30 kok gak bisa ya mas error nya di sql syntax-nya

    ReplyDelete
    Replies
    1. ralat mas saya cma lupa tanda koma saja ,, hpus saja komennya :D

      Delete
    2. Alhamdulilah,kalau sudah dapat solusinya

      Delete
  27. This comment has been removed by the author.

    ReplyDelete
  28. ikutan nimbrung mas... Gimana agar record tidak ikut berubah ketika file master yang berhubungan diganti? Misal semula Denda telat Perpus 500, hari ini diganti 1000, gimana agar hari yang telah lalu tidak ikut2an ganti 1000 ? matur nuwun ...

    ReplyDelete
    Replies
    1. Kalau menurut saya kalau tentang denda di perpusatakan, maka di tabel yang menyimpan jumlah denda buku adalah bukan id table master denda tetapi nilai dari denda dari table master

      Delete
  29. misalnya di database saya pnya tanggal a=2013-03-28 dan b=2013-03-30, nah sya mau menampilkan seluruh tanggal yang ada dalam range tersebut, caranya gimana y mas??

    miisalny hasil ny seperti ini :
    2013-03-28
    2013-03-29
    2013-03-30

    ReplyDelete
  30. saya mau numpang tanya mas..
    kalo misal untuk mengubah dari format time ke double bisa ga?
    makasi

    ReplyDelete
  31. baagus ini mas dendie postingnya.....
    nanya nih mas...,
    misalnya saya punya sebuah tabel yang berisi 3 field(kolom) dimana terdiri dari

    ||tglawal || tglakhir || selisihtgl ||

    maksud saya adalah jika dimasukkan tgl pada field tglawal dan tglakhir maka selisihtgl otomatis akan terisi jumlah dari hitungan keduanya(satuanyya adalah hari )

    gmn mas ? mohon pencerahannya?

    ReplyDelete
    Replies
    1. Apabila menghitung selisih tanggal nya inggin di MYSQL maka di perlukan 2 kali eksekusi query.

      1. Query pertama insert tglawal dan tanggal akhir.
      2. Query kedua updae field selisihtgl.

      Delete
    2. mohon maaf gan...sebelumnya....contohnya seperti apa ya gan..?
      saya masih bingung dengan yang agan jelasin...terima kasih sudah respon saya

      Delete
  32. gan mau nanya kalo cara nampilin usia dari tanggal lahir yang umurnya hanya dari 20-30 gmn?
    makasih sebelumnya..

    ReplyDelete
  33. trims mas waktunya..
    pertanyaanku tentang tglawal||tglakhir||selisihtgl
    sudah dijelasin...setahuku mas kalo yang dipake sql server 2005 ke atas itu sudah ada hitungan selisihtgl dngan menggunakan klausa AS bla..bla..

    ini ane mau tanya lagi :
    kasusnya gini gan...misalnya ada tabel yang memiliki 2 field ..
    field pertama : idfilm
    field kedua : judulfilm
    yg tak tanyain gmn gan agar 1 buah idfilm bisa memiliki lebih dari satu judulfilm dengan kata lain bagaimana agar sebuah primary key , kolom disampingnya bisa memiliki atribut row lebih dari satu baris?

    ReplyDelete
  34. Bagus banget nih Mas Dendie, ane lagi nyari script tentang menghitung umur dan nampilin di report.
    Thanks Mas Dendie

    ReplyDelete
  35. maaf, mau tanya, gmn script-nya klo mau nyari tanggal, bualan, hari, yang diambil dari tabel RS15 dan RS23???
    terima kasih sebelumnya...^_^

    ReplyDelete
  36. maaf mas saya mau nanya, bagaimana jika menghitung jumlah pegawai yang absen masuknya telat? batas waktu absen jam 09:45. jadi jika pegawai telat langsung di potong gaji. gimana ya mas solusinya?

    terima kasih.

    ReplyDelete
  37. hgkyttghgjllllllllll;;;;;;y6786ytfghfluyytyti75675ytruytryue

    ReplyDelete
  38. bagaimana cara menghitung jumlah karyawan yang datang di atas jam 21.00 dan di bawah 08.00

    ReplyDelete
  39. Bro.. maap ganggu.
    ane udah query kya di bawah.

    SEC_TO_TIME(SUM(TIME_TO_SEC(timediff(tqueue.waktu_dipanggil , tqueue.waktu_ambil_tiket) ) ) ) as total_waktu.

    hasil nya 267:14:49 ratusan jam.
    kalo ane masukin di Ireport kan ga masuk tuh.
    ada cara nya ga query ane itu jadi TIMESTAMP atau DATETIME

    ReplyDelete
  40. gan mau nanya klw mau nyari selisih tanggal terus selisihnya kita jadi kan validasi gimana yach?

    saya pnya kasus pada aplikasi asuransi jika nasabah melakukan pembayaran lewat dari tnggl janji bayar (account status promise to pay) maka account status berubah mnjadi broken promise

    gimana ya gan mohon solusinya...

    ReplyDelete
  41. mau nnya gan..
    sintak untk nampilin umur tpi seperti : tahun, bulan, hari

    gmna dia gan???
    mohon bantuannya....

    ReplyDelete
  42. sorry gan klo cara nya agar banyak dendaan otomatis berdasar kan keterlambatan gimana..?

    ReplyDelete
  43. ini dia salah satu fingsi yang bakal sering saya pakai

    ReplyDelete
  44. fungsi ini sama gak buat di sql server?

    ReplyDelete
  45. Ini penjelasan yang saya cari, dilihat dari komennya juga sangat membantu

    ReplyDelete
  46. gan klo fungsi tersebut selisihnya di simpan dalam database mysql gmn? misal $carikontrak = "SELECT *,current_date(mulai_masuk)AS mulai_kontrak, DATEDIFF(tgl_akhir_kontrak, mulai_kontrak)AS habis_kontrak FROM t_karyawan";

    $selisih=$this->db->query($carikontrak);

    nah kira2 yang ($selisih) saya insert ke rows table, jadi buka menggunakan select. gmn ya gan? tolong dong agan2 master php dan framework?

    ReplyDelete
  47. Sy mau buat tanggal masehi jadi hijriyah, lalu dalam tanggal hijriyah sekarang misal: 1 syawal 1437, maka 1 syawal 1438 tahun masehinya jadi tanggal berapa?
    Perintah mysqlnya bgmn??

    ReplyDelete
  48. Sy mau buat tanggal masehi jadi hijriyah, lalu dalam tanggal hijriyah sekarang misal: 1 syawal 1437, maka 1 syawal 1438 tahun masehinya jadi tanggal berapa?
    Perintah mysqlnya bgmn??

    ReplyDelete
  49. Assalamualaikum....
    Gan z mw tx bagaimana menampilkan kalau sudah saatnya pensiun..., misalnya batas tanggal pensiun umur 65 tahun...., dan hasilnya itu akan muncul secara otomatis....
    Makasih gan.....

    ReplyDelete
  50. menghitung selisih jam 23:54:08 malam dan jam 01:00:55 malam gmana?? klau pake TIMEDIFF kan hasilnya 22:53:13

    ReplyDelete
  51. sorry gan mau tanya
    kalo mau cari denda keterlambatan kan tinggal yang "DATEDIFF(NOW(),tgl_batas_pinjam)" dikali jumlah denda. tapi masalahnya hasilnya akan negatif kalo telat dan kalo belum telat hasilnya bukan 0.. nah untuk membuat hasilnya menjadi 0 kalo belum telat gimana ya?? hehe makasih

    ReplyDelete
  52. gan, ijin tanya tentang presensi. jika karyawan melakukan presensi jam 8.15 am, sedangkan jam masuk kerja jam 8.00, nah untuk menampilkan waktu 15 menit itu gmn ya gan ?

    ReplyDelete
  53. hallo gan, ijin bertanya untuk menghitung dendanya bagaimana? misal lebih dari tgl_pinjam secara auto di dikalikan dgn perhari

    ReplyDelete
  54. Bagaimana menghitung denda perpustakaan

    ReplyDelete
  55. This comment has been removed by the author.

    ReplyDelete