SQLite3, very compact and very useful

SQLite3 is a marvellously useful tool. I've seen it used in finance, image management, system admin...

It's estimated there are billions of SQLite DBs in use all over the world. Mostly because it's used in Android and Apple phones... there are billions of Android phones alone. Each of these devices use SQLite to make data management easier.

But out of the box, it doesn't understand month names

SQLite can import and manipulate dates in several formats. It can generate date strings fairly well. One thing the date handling doesn't have is a function or an SQLite3 based strftime format code to translate a month's number to its name.

For developers, month numbers are usually fine (though I can admit to doing quick hand counts mapping month names to numbers) to use when dealing with date based information.

If your app or tool aims to be straightforward to use, month names are probably easier to understand if you need to display or read date information with your SQLite3 DB.

Storing month names in a DB table

The following scripts use coreutils date to generate a list of month numbers and corresponding month names. Once this script is run on the DB, month names are available in the table month_numbers_and_names.

The table can be created on a newly created database, or an existing database.

Save the following snippet of code as something like month_names_from_nums.sh....

function month_names() {
 month_num=1  #January, in English
 echo month_number, month_name
 while [[ ${month_num} -lt 13 ]]; do 
   date --date="2000-$(printf "%02d" $month_num)-01" +%m,%B;
   ((month_num++)); 
 done
}

Save the script below as import_dates.sql...

.separator ","
  .import month_numbers_and_names.csv month_numbers_and_names

Create an SQLite3 db, or use an existing one

month_names > month_numbers_and_names.csv
sqlite3 my_db.db < import_dates.sql

Once this is done, you can open up my_db.db and you'll find the month names in the table month_numbers_and_names...

sqlite3 my_db.db "select * from month_numbers_and_names;"
month_number   month_name
------------  -----------
01            January    
02            February   
03            March      
04            April      
05            May        
06            June       
07            July       
08            August     
09            September  
10            October    
11            November   
12            December

Merci, other languages can be supported as well

Conveniently, date will generate month names for other locales if they're needed. To get French dates, we can use the following...

LANG=fr_FR month_names > month_numbers_and_names.csv
sqlite3 my_french_db.db < import.dates.sql

sqlite3 my_french_db.db "select * from month_numbers_and_names;"

month_number   month_name
------------  -----------
01            janvier    
02            février    
03            mars       
04            avril      
05            mai        
06            juin       
07            juillet    
08            août       
09            septembre  
10            octobre    
11            novembre   
12            décembre

This can be handy if you have dates going in and out of your app.

A simpler date passing interface can be had if month names are used. More processing may be able to be done directly on the database, without calling on date libraries.

So, month name translation for dates is missing in SQLite3, but it's relatively straightforward to add. Internationalisation is possible too (a month_numbers_and_names.locale field would be required if month translations between locales was needed). This is left as an exercise for the reader :-).

Can this concept be used with other data too?

Think about other translations and mappings that occur in your app... Could moving this data to a table in SQLite3 make the code lighter, the data more easily available ?