macOS SQLite 3 date() Function

[montanaproducts]

This is my first post so go easy on me! I'm teaching myself SQLite 3 and entering all commands in "Terminal". I'm using Sams Teach Yourself SQL in 10 Minutes which covers SQL in general but not SQLite. I'm extracting a date from a database and using the following syntax:

sqlite> SELECT order_num FROM Orders WHERE order_date BETWEEN date('2004-01-01') AND date('2004-12-31');

The object is to extract "All Orders" from the year "2004", this produces the correct results as expected, however I'd like a cleaner form such as:

sqlite> SELECT order_num FROM Orders WHERE date(YYYY, order_date) = 2004;

This doesn't work! I know the form is:
date(timestring, modifier, modifier, ...) however I can't get it right. Your help is appreciated.

John

 

[lee1210]

This sort of thing is almost always specific to your database system. In postgres there's a date_part function, it looks like you'll want to use strftime in SQLite.

http://www.sqlite.org/lang_datefunc.html

This page details it... it would be something like:
SELECT order_num FROM Orders WHERE strftime('%Y', order_date) = '2004';

-Lee

 

[lee1210]

sorry for the double post, but I thought this was worth mentioning. I don't know what indexing options are available for SQLite, and how advanced the query planner (I assume there is one or something similar) is. However, using a function as part of a predicate may result in an index being ignored (on order_date, for example). You may be able to make an index on the result of a function, but this will depend on SQLite's abilities. You may have to put in extra effort for this, such as writing a function that takes a date and returns the year with no additional parameters.

This all may be moot, but i just wanted to point out that human readability may result in suboptimal performance.

-Lee

 

[montanaproducts]

Lee

Thanks-that worked perfectly. I was on the right page but didn't interpret the info correctly. Of course SQLite is an embedded database that I plan to use with REALbasic for a front end. I'm trying to make certain my basic DB concepts are correct before dealing with the interface, so I'm doing all this in terminal (command line). Nothing's right if your logic is wrong.

John