Looking for a Few Good Examples

In the #maria IRC channel the other day I fielded a question someone had about a DATETIME column where they wanted to SELECT by the year. The answer (which is probably obvious to many of you) is to use the YEAR() function like so:

select * from t1 where YEAR(d) = 2011;

(The above assumes the table is named “t1” and the DATETIME column is named “d“.)

In my reply I provided a link to the Date and Time Functions section of the AskMonty Knowledgebase, but when I looked at the entry for the YEAR() function, I noticed that the example given (which originated from the file scripts/fill_help_tables.sql which is found in the MySQL and MariaDB source) was not very helpful:

MariaDB [(none)]> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
|               1987 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

The above is certainly a valid use of the YEAR() function, but it’s not a real-world use (at least, I don’t know why anyone would use the above SELECT statement in a real application). So I added an example which I feel is more useful, especially to someone unfamiliar with using SQL functions:

CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES
    ("2007-01-30 21:31:07"),
    ("1983-10-15 06:42:51"),
    ("2011-04-21 12:34:56"),
    ("2011-10-30 06:31:41"),
    ("2011-01-30 14:03:25"),
    ("2004-10-07 11:19:34");
MariaDB [test]> select * from t1;
+---------------------+
| d                   |
+---------------------+
| 2007-01-30 21:31:07 |
| 1983-10-15 06:42:51 |
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
| 2004-10-07 11:19:34 |
+---------------------+
6 rows in set (0.02 sec)

MariaDB [test]> select * from t1 where YEAR(d) = 2011;
+---------------------+
| d                   |
+---------------------+
| 2011-04-21 12:34:56 |
| 2011-10-30 06:31:41 |
| 2011-01-30 14:03:25 |
+---------------------+
3 rows in set (0.09 sec)

Looking at the entries for other functions in both the Date and Time functions section and elsewhere there is a pattern of examples that are useful, but — like the original YEAR() example — useless in the real world.

I have now added better examples to several of the date and time functions but they can probably be improved even more and there are many more entries in the Knowledgebase that would also benefit from better examples. The good news is adding examples to Knowledgebase entries is very easy to do. The bad news is that if I think of all of the examples on my own they will be similar and probably boring, so I’d like to ask for some help.

If you’ve ever wanted to contribute to MariaDB, but didn’t know where to begin, I have a suggestion: Look under the Functions and Operators section of the AskMonty Knowledgebase, find a function or two in need of better examples, and add some (either directly or via a comment). Don’t worry if you are unfamiliar with editing the Knowledgebase. I can clean things up and make your examples look pretty after the fact. Just make sure your awesome new examples actually work. 🙂

Thanks!

Published by MariaDB Foundation

Daniel Bartholomew is the MariaDB Release Manager. He has written two MariaDB-related books: Getting Started with MariaDB (now in it's 2nd Edition) and the MariaDB Cookbook, both published by Packt.