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 ?
Top comments (3)
Wouldn't if be better to create a custom function?
Then you can have queries like
select month_en(strftime('%m')) from table.I think this is a better option than needing a join to get the month name.
Yes, thanks for that. SQLite is designed to be embedded into another executable, so a custom function is a very natural way to solve the problem.
It might partly come down to ones own preference and skillset. Setting up a custom function needs a C development skillset, which some people might not have.
I found setting up data in this way fairly straightforward and thought I'd share the bash/sqlite3 dance of automating it. I also find myself thinking "relationally" a lot when I'm working with data these days.
Good to think of other ways of doing the same thing.
In the last part of the post I talk about other pieces of data that could be imported in this way as well. I like that this is so straightforward in SQLite3.
Thanks again,
Kreely
I know in php and in python you can create functions without using C.