Mengeksplorasi Fungsi Tanggal di SQLite

Beberapa fungsi paling berguna dalam implementasi SQL pada Postgres (seperti Amazon Redshift) adalah DATE_DIFF dan DATE_TRUNC:

• DATE_DIFF memberikan jumlah waktu yang berlalu antara dua tanggal berbeda. Misalnya, kode berikut akan memberikan jumlah hari antara date1 dan date2:

DATE_DIFF('day', date1, date2)
Didukung oleh

DATE_DIFF sangat baik untuk menghitung jumlah hari dari pendaftaran hingga pembatalan atau jumlah jam dari masuk hingga keluar.

• DATE_TRUNC memangkas sebuah tanggal ke hari, minggu, bulan, atau tahun terdekat. Misalnya, kode berikut akan memberikan Senin terdekat untuk stempel waktu my_timestamp:

DATE_TRUNC('week', my_timestamp)
Didukung oleh

DATE_TRUNC sangat berguna untuk mengagregasi data. Misalnya, kita dapat menggunakannya untuk menemukan jumlah Monthly Active Users (MAU) dengan memangkas ke tanggal mulai bulan terdekat.

Namun tidak setiap implementasi SQL memiliki fungsi-fungsi hebat ini. Untuk kode Learn SQL from Scratch kami, kami menggunakan SQLite, sebuah implementasi SQL yang ringan dan dapat dijalankan pada satu instance Docker. SQLite sangat cocok untuk backend situs web dan proyek kecil, tetapi tidak memiliki dua fungsi favorit saya. Untungnya, ada cara mengakalinya.

Untuk meniru DATE_DIFF, kita dapat menggunakan fungsi yang kurang dikenal bernama juliandate. Menurut Wikipedia, "Julian Day Number (JDN) adalah bilangan bulat yang diberikan untuk satu hari matahari penuh dalam hitungan hari Julian mulai dari tengah hari waktu Universal, dengan nomor hari Julian 0 diberikan untuk hari yang dimulai pada tengah hari Senin, 1 Januari, 4713 SM". Dengan mengonversi tanggal menjadi angka pecahan, kita dapat menggunakan pengurangan untuk menemukan selisih antara dua stempel waktu.

Kita bahkan dapat mengonversi jawabannya ke jam dengan mengalikan dengan 24 atau ke menit dengan mengalikan dengan 24 * 60.

Kita dapat meniru sebagian fungsi DATE_TRUNC dengan menggunakan strftime. Fungsi ini mengonversi stempel waktu menjadi string dengan keluaran tertentu.

%d hari dalam bulan: 00 %f detik pecahan: SS.SSS %H jam: 00-24 %j hari dalam tahun: 001-366 %J nomor hari Julian %m bulan: 01-12 %M menit: 00-59 %s detik sejak 1970-01-01 %S detik: 00-59 %w hari dalam minggu 0-6 dengan Minggu==0 %W minggu dalam tahun: 00-53 %Y tahun: 0000-9999

Biasanya, kita mungkin menggunakannya untuk konversi antar format stempel waktu seperti YYYY-MM-DD ke MM-DD-YYYY:

strftime('%M-%D-%Y', mydate)
Didukung oleh

Tetapi kita dapat dengan cerdas memilih pemformatan untuk memangkas ke titik yang sesuai. Misalnya, jika kita ingin memangkas ke bulan terdekat, kita dapat:

strftime('%M/%Y', mydate)
Didukung oleh

Atau kita dapat memangkas ke minggu terdekat dengan menggunakan:

strftime('%Y-%w', mydate)
Didukung oleh

Dengan dua trik mudah ini, Anda dapat menggunakan SQLite untuk beberapa analisis hebat yang sama seperti Amazon Redshift!

Jika Anda ingin mempelajari lebih lanjut tentang dasar-dasar SQL, ikuti kursus Intro to SQL for Data Science dari DataCamp dan lihat SQL Tutorial for Beginners kami.