Panduan Tanggal dan Waktu MySQL: Tipe Data, Fungsi, Pemformatan, dan Kuery Rentang

1. Introduction

MySQL adalah sistem manajemen basis data yang banyak digunakan untuk membangun aplikasi web dan basis data, dan penanganan data tanggal dengan tepat sangat penting. Misalnya, data yang berhubungan dengan tanggal muncul dalam banyak skenario: mengelola posting blog, melacak riwayat penjualan produk, dan menyimpan riwayat login pengguna. Dengan mengelola data tanggal secara benar, Anda dapat memproses data secara efisien dan memberikan informasi yang akurat kepada pengguna.

Dalam panduan ini, kami akan menjelaskan secara komprehensif segala hal mulai dari tipe data tanggal/waktu dasar dan cara menggunakan fungsi tanggal di MySQL hingga perhitungan berbasis tanggal dan kueri rentang. Konten ini dirancang untuk pengguna pemula hingga menengah, dan kami akan menyertakan contoh kueri praktis berdasarkan kasus penggunaan dunia nyata—menjadikannya berguna untuk pekerjaan sehari-hari.

Dengan membaca artikel ini, Anda akan dapat melakukan hal berikut:

• Memahami karakteristik tipe data tanggal/waktu MySQL dan memilih tipe yang tepat untuk data Anda.
• Menggunakan fungsi tanggal untuk dengan mudah mengambil dan memanipulasi tanggal saat ini, tanggal masa lalu, dan tanggal masa depan.
• Mengekstrak data untuk periode tertentu dengan melakukan pencarian dan perbandingan rentang tanggal.

Sekarang, mari kita lihat dasar‑dasar tanggal MySQL mulai dari bagian berikutnya.

2. Overview of MySQL Date/Time Data Types

Saat mengelola tanggal di MySQL, penting untuk memilih tipe data tanggal/waktu yang tepat tergantung pada data dan kasus penggunaannya. MySQL menyediakan beberapa tipe data untuk menangani tanggal dan waktu, masing‑masing dengan karakteristik dan penggunaan yang berbeda. Pada bagian ini, kami akan menjelaskan tipe tanggal/waktu utama dan contoh penggunaan tipikalnya secara detail.

DATE

Tipe DATE menyimpan hanya tanggal (tahun, bulan, hari) dan tidak menyertakan komponen waktu. Rentang yang didukung adalah dari “1000-01-01” hingga “9999-12-31”, sehingga dapat menangani tanggal dari tahun 1000 hingga 9999. Tipe ini cocok untuk informasi yang hanya memerlukan tanggal, seperti ulang tahun atau hari jadi.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Tipe TIME menyimpan waktu (jam, menit, detik). Rentang yang didukung adalah dari “-838:59:59” hingga “838:59:59”. Karena dapat merepresentasikan waktu negatif, tipe ini juga dapat digunakan untuk menyatakan selisih waktu. Tipe ini berguna untuk mencatat jam kerja atau durasi tugas.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Tipe DATETIME menyimpan baik tanggal maupun waktu. Rentang yang didukung adalah dari “1000-01-01 00:00:00” hingga “9999-12-31 23:59:59”. Tipe ini tidak bergantung pada zona waktu, dan Anda dapat mengambil nilai yang disimpan persis seperti yang disimpan. Tipe ini cocok untuk mencatat stempel waktu (timestamp) peristiwa masa lalu atau jadwal masa depan.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Tipe TIMESTAMP menyimpan tanggal dan waktu serta secara otomatis mengonversi nilai berdasarkan zona waktu server. Rentang yang didukung adalah dari “1970-01-01 00:00:01 UTC” hingga “2038-01-19 03:14:07 UTC”. Tipe ini kompatibel dengan waktu epoch Unix dan cocok untuk mencatat stempel waktu serta riwayat perubahan. Selain itu, karena TIMESTAMP dapat mengatur waktu saat ini secara default, tipe ini memudahkan pencatatan otomatis waktu pembuatan dan pembaruan.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Tipe YEAR menyimpan hanya tahun. Rentang yang didukung adalah dari “1901” hingga “2155”. Tipe ini berguna ketika Anda ingin merepresentasikan tahun tertentu, seperti tahun produksi atau tahun pendirian.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparison and Use Cases of Each Date/Time Type

Summary

MySQL date/time data types should be chosen based on the characteristics of your data and how you plan to use it. In the next section, we’ll take a closer look at the differences between DATETIME and TIMESTAMP, which are especially easy to confuse.

3. Perbedaan Antara DATETIME dan TIMESTAMP

Saat bekerja dengan tanggal dan waktu di MySQL, DATETIME dan TIMESTAMP sering digunakan, tetapi ada perbedaan penting di antara keduanya. Keduanya menyimpan tahun, bulan, hari, jam, menit, dan detik, tetapi berbeda dalam cara mereka menangani zona waktu dan rentang waktu yang dapat disimpan. Anda harus memilih berdasarkan kasus penggunaan Anda. Pada bagian ini, kami akan menjelaskan perbedaan dan karakteristik DATETIME dan TIMESTAMP secara detail.

Karakteristik DATETIME

• Tidak bergantung pada zona waktu : Tipe DATETIME tidak dipengaruhi oleh pengaturan zona waktu server. Anda dapat mengambil nilai yang disimpan persis seperti yang disimpan.
• Rentang : Mendukung dari 1000-01-01 00:00:00 hingga 9999-12-31 23:59:59.
• Kasus penggunaan : Cocok untuk data yang ingin Anda simpan tanpa bergantung pada zona waktu tertentu, seperti peristiwa historis atau jadwal masa depan.

Contoh: Menyimpan tanggal/waktu acara

Misalnya, jika Anda ingin mempertahankan nilai tanggal/waktu “universal” apa adanya, DATETIME adalah pilihan yang baik. Contoh berikut mencatat sebuah acara yang dijadwalkan pada tanggal dan waktu tertentu.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

Dalam tabel ini, tanggal/waktu yang disimpan di event_datetime tidak dipengaruhi oleh zona waktu, dan nilai tersebut diambil persis seperti yang disimpan.

Karakteristik TIMESTAMP

• Bergantung pada zona waktu : Tipe TIMESTAMP disimpan dan diambil berdasarkan zona waktu server. Saat memindahkan data antara server dengan zona waktu yang berbeda, konversi akan terjadi sesuai.
• Rentang : Mendukung dari 1970-01-01 00:00:01 UTC hingga 2038-01-19 03:14:07 UTC (berdasarkan rentang waktu Unix).
• Dukungan auto‑update : Dengan menggunakan DEFAULT CURRENT_TIMESTAMP dan ON UPDATE CURRENT_TIMESTAMP, MySQL dapat secara otomatis mencatat timestamp saat ini ketika menyisipkan atau memperbarui data.
• Kasus penggunaan : Terbaik untuk melacak secara otomatis waktu pembuatan atau pembaruan, seperti ketika Anda menginginkan timestamp berubah setiap kali sebuah record diperbarui.

Contoh: Menyimpan waktu dibuat dan diperbarui untuk posting

Contoh ini menggunakan fitur auto‑update dari TIMESTAMP untuk mencatat waktu pembuatan dan pembaruan untuk posting blog.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Dengan pengaturan ini, created_at dicatat saat posting pertama kali dibuat, dan updated_at secara otomatis diperbarui setiap kali posting diedit.

Tabel Perbandingan: DATETIME vs TIMESTAMP

Cara Memilih

• Pilih DATETIME jika Anda tidak menginginkan efek zona waktu : Misalnya, jika Anda ingin menyimpan waktu acara sebagai waktu lokal tetap untuk negara/daerah tertentu, DATETIME dapat menjadi pilihan yang tepat.
• Pilih TIMESTAMP jika Anda membutuhkan auto‑update atau penanganan zona waktu : Misalnya, jika Anda ingin secara otomatis mencatat kapan baris basis data diperbarui, TIMESTAMP sangat praktis.

Ringkasan

DATETIME dan TIMESTAMP memiliki karakteristik yang berbeda. Dengan memilih yang tepat untuk tujuan Anda, Anda dapat mengelola data tanggal/waktu lebih efisien. Pada bagian berikutnya, kita akan melihat fungsi tanggal penting di MySQL.

4. Dasar-dasar Fungsi Tanggal/Waktu MySQL

MySQL menyediakan banyak fungsi untuk bekerja dengan tanggal dan waktu—dari mengambil tanggal/waktu saat ini hingga menambah atau mengurangi interval. Fungsi-fungsi ini berguna untuk administrasi basis data dan analisis. Pada bagian ini, kami akan menjelaskan dasar-dasar fungsi tanggal/waktu MySQL dan kasus penggunaan tipikalnya.

Fungsi untuk Mendapatkan Tanggal/Waktu Saat Ini

Tiga fungsi umum untuk mengambil tanggal dan waktu saat ini di MySQL adalah NOW(), CURDATE(), dan CURTIME().

NOW()

Fungsi NOW() mengembalikan tanggal dan waktu saat ini dalam format YYYY-MM-DD HH:MM:SS. Fungsi ini berguna untuk pencatatan log dan penandaan waktu pada record.

SELECT NOW(); -- Get the current date and time

CURDATE()

Fungsi CURDATE() mengembalikan tanggal saat ini dalam format YYYY-MM-DD. Tidak termasuk waktu, sehingga cocok ketika Anda hanya membutuhkan tanggal.

SELECT CURDATE(); -- Get the current date

CURTIME()

Fungsi CURTIME() mengembalikan waktu saat ini dalam format HH:MM:SS. Gunakan ketika Anda hanya membutuhkan waktu tanpa tanggal.

SELECT CURTIME(); -- Get the current time

Fungsi untuk Menambah/Mengurangi Tanggal

Untuk menambah atau mengurangi interval dari sebuah tanggal, gunakan DATE_ADD() dan DATE_SUB(). Fungsi-fungsi ini memudahkan perhitungan tanggal di masa depan atau masa lalu.

DATE_ADD()

DATE_ADD() menambahkan interval tertentu ke sebuah tanggal. Misalnya, berguna untuk mendapatkan tanggal 7 hari ke depan atau 1 bulan ke depan.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

DATE_SUB() mengurangi interval tertentu dari sebuah tanggal. Gunakan untuk menghitung tanggal di masa lalu.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Fungsi untuk Menghitung Selisih Tanggal

Untuk menghitung selisih antara dua tanggal, DATEDIFF() sangat membantu. Misalnya, Anda dapat menghitung berapa hari yang telah berlalu sejak tanggal tertentu, atau jumlah hari antara dua tanggal.

DATEDIFF()

DATEDIFF() mengembalikan selisih antara dua tanggal dalam hari. Berguna ketika Anda ingin memastikan jumlah hari dari tanggal mulai hingga tanggal akhir.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Fungsi Tanggal Lain yang Berguna

MySQL menyediakan fungsi tanggal tambahan yang berguna.

EXTRACT()

EXTRACT() mengekstrak bagian tertentu (tahun, bulan, hari, dll.) dari sebuah tanggal. Berguna ketika Anda hanya membutuhkan sebagian dari tanggal.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

DATE_FORMAT() menampilkan tanggal dalam format yang ditentukan. Berguna ketika Anda ingin menampilkan tanggal dalam format tertentu (misalnya, format gaya Jepang).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Metode Pemformatan dan Konversi Tanggal

Di MySQL, Anda dapat mengubah cara tanggal ditampilkan menjadi format yang lebih mudah dibaca, atau mengonversi string tanggal menjadi tipe tanggal. Hal ini memungkinkan Anda mengontrol format tampilan secara fleksibel dan mengelola data secara konsisten meskipun masukan datang dalam format yang berbeda. Pada bagian ini, kami akan menjelaskan fungsi utama yang digunakan untuk pemformatan dan konversi tanggal.

Pemformatan Tanggal dengan Fungsi DATE_FORMAT()

Dengan menggunakan DATE_FORMAT(), Anda dapat menampilkan nilai tanggal dalam format yang ditentukan. Ini sangat berguna ketika Anda memerlukan format tampilan selain standar, seperti format Jepang “YYYY年MM月DD日”.

Opsi Format

Dengan DATE_FORMAT(), Anda dapat menggunakan opsi format seperti berikut:

• %Y : tahun 4 digit
• %y : tahun 2 digit
• %m : bulan 2 digit (01–12)
• %d : hari 2 digit (01–31)
• %H : jam 2 digit (00–23)
• %i : menit 2 digit (00–59)
• %s : detik 2 digit (00–59)

Contoh Penggunaan

Misalnya, untuk menampilkan tanggal 2023-01-01 sebagai “2023年01月01日,” Anda dapat menulis seperti berikut:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Anda juga dapat menampilkan tanggal dan waktu dengan garis miring, seperti “2023/01/01 12:30:45.”

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Mengonversi String menjadi Tanggal dengan STR_TO_DATE()

Fungsi STR_TO_DATE() mengonversi string tanggal menjadi tipe tanggal MySQL. Misalnya, berguna ketika Anda ingin memperlakukan string seperti “2023年01月01日” sebagai nilai DATE.

Contoh Penggunaan

Untuk mengonversi string “2023-01-01” menjadi nilai DATE, tulis berikut ini:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Untuk mengonversi string bergaya Jepang seperti “2023年01月01日” menjadi tanggal, tentukan format yang sesuai:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Contoh-contoh Mengonversi Tanggal dari Berbagai Format

Dalam penggunaan dunia nyata, format input tanggal dapat bervariasi, sehingga Anda mungkin perlu mengonversi dari format yang berbeda. Berikut beberapa contoh.

1. Konversi “YYYY/MM/DD” menjadi nilai DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Konversi “MM-DD-YYYY” menjadi nilai DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Dengan cara ini, meskipun data dimasukkan dalam format yang berbeda, Anda dapat menyimpan dan mengelolanya secara konsisten dalam format tanggal yang terpadu.

Perbedaan Antara DATE_FORMAT dan STR_TO_DATE, serta Kapan Menggunakan Masing‑Masing

• DATE_FORMAT() : Digunakan untuk mengubah format tampilan nilai tanggal yang sudah ada. Karena perubahan hanya memengaruhi tampilan, data yang disimpan tidak diubah.
• STR_TO_DATE() : Digunakan untuk mengonversi string tanggal menjadi nilai DATE atau DATETIME MySQL. Ini mengubah tipe data yang disimpan dan memudahkan pemrosesan tanggal dengan fungsi dan kueri MySQL lainnya.

Ringkasan

Dengan menggunakan DATE_FORMAT() dan STR_TO_DATE(), Anda dapat mengelola cara data tanggal ditampilkan dan disimpan secara fleksibel. Hal ini memungkinkan penanganan input pengguna atau sistem dengan lebih fleksibel sambil mempertahankan manajemen tanggal yang konsisten. Pada bagian berikutnya, kami akan menjelaskan perhitungan dan perbandingan tanggal, serta melihat lebih dekat cara menghitung dan membandingkan periode menggunakan data tanggal.

6. Perhitungan dan Perbandingan Tanggal

MySQL menyediakan beberapa fungsi yang praktis untuk perhitungan dan perbandingan tanggal. Fungsi‑fungsi ini memungkinkan Anda mengekstrak data untuk periode tertentu atau menghitung selisih tanggal untuk analisis. Pada bagian ini, kami akan memperkenalkan fungsi utama yang digunakan untuk perhitungan dan perbandingan tanggal, beserta cara menggunakannya.

Fungsi untuk Menghitung Selisih Tanggal: DATEDIFF()

Fungsi DATEDIFF() mengembalikan selisih antara dua tanggal dalam “hari.” Fungsi ini berguna ketika Anda ingin memeriksa berapa hari yang telah berlalu antara tanggal atau menentukan berapa hari yang telah lewat sejak suatu peristiwa hingga hari ini.

Contoh Penggunaan

Sebagai contoh, untuk menghitung jumlah hari dari 1 Januari 2023 hingga 10 Januari 2023, tulis berikut ini:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Dalam contoh ini, selisih dari tanggal mulai ke tanggal akhir adalah 9 hari, sehingga hasilnya adalah 9.

Menghitung Selisih per Unit dengan TIMESTAMPDIFF()

Fungsi TIMESTAMPDIFF() menghitung selisih antara dua tanggal atau datetime dalam unit yang ditentukan (tahun, bulan, hari, jam, menit, detik). Misalnya, Anda dapat menghitung selisih dalam “bulan” atau “jam” sesuai kebutuhan.

Contoh Penggunaan

1. Hitung selisih dalam bulan

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Hitung selisih dalam jam

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Membandingkan Tanggal

Perbandingan tanggal dilakukan menggunakan operator perbandingan standar MySQL (<, >, <=, >=, =). Hal ini memungkinkan Anda mengekstrak data dalam periode tertentu atau menyertakan tanggal masa lalu/masa depan sebagai kondisi.

Contoh Penggunaan

Sebagai contoh, untuk mengekstrak data “pada atau setelah 1 Januari 2023,” tulis berikut ini:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Menentukan Rentang dengan BETWEEN

Operator BETWEEN memungkinkan Anda menentukan rentang tanggal untuk mengambil data. Ini berguna ketika Anda ingin mengekstrak catatan yang berada dalam periode tertentu.

Example Usage

Sebagai contoh, untuk mengambil data dari 1 Januari 2023 hingga 31 Januari 2023, tulis berikut:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Perhitungan Tanggal dengan ADDDATE() dan SUBDATE()

Anda dapat menggunakan ADDDATE() dan SUBDATE() untuk menambah atau mengurangi sejumlah hari dari sebuah tanggal. Ini sangat berguna untuk menghitung tanggal di masa depan atau masa lalu.

Example Usage

1. Tambahkan 10 hari ke sebuah tanggal

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Kurangi 10 hari dari sebuah tanggal

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Ringkasan

Dengan menggunakan fitur perhitungan dan perbandingan tanggal MySQL, Anda dapat mengekstrak data untuk periode tertentu secara efisien dan menganalisis data berdasarkan rentang waktu. Pada bagian berikutnya, kita akan melihat lebih dekat topik lanjutan “pencarian rentang tanggal.”