SQLite 3 date() Function

Discussion in 'Mac Programming' started by montanaproducts, Nov 22, 2008.

  1. montanaproducts macrumors newbie

    Joined:
    Nov 22, 2008
    #1
    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
     
  2. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #2
    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
     
  3. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #3
    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
     
  4. montanaproducts thread starter macrumors newbie

    Joined:
    Nov 22, 2008
    #4
    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
     

Share This Page