SQLite - Select statement to extract 1 year of data

Question

Created Jul ’23

Replies 5

Boosts 0

Participants 2

I have an SQLite table that holds a fund name, as a string, a time stamp, as an iso8601 string, and a close, as a double. SQLite appears to call strings text and a double a real. In the function below, I query this table with a select statement to return the last year of data based on the latest date in the table. To get the latest date I have a separate function that finds the latest date, converts if from a string to a date, subtracts 1 year from the date then converts it back to an iso8601 date string. It is this date string that is being passed into the function below as the variable startingDate. The function below works just fine and is relatively fast. What I am trying to determine is there a way to modify the select statement in the function below such that it does everything and I will no longer have to separately calculate the starting date and pass it in?

func QuerySQLiteData(db: OpaquePointer?, fundName: String, startingDate: String) -> [TradingDay] {
    
    var queryResults: [TradingDay] = []
    let timeStampFormatter = ISO8601DateFormatter()
    
    let queryTradingDaysStatement = """
    Select
        FundName,
        TimeStamp,
        Close
    FROM
        TradingDays
    WHERE
        FundName = '\(fundName)'
        AND
        TimeStamp >= '\(startingDate)'
    ORDER By
       TimeStamp ASC
    ;
    """
    var queryTradingDaysCPtr: OpaquePointer?
    var tradingDay: TradingDay = TradingDay(fundName: "", timeStamp: Date(), close: 0.0)
    
    if sqlite3_prepare_v2(db, queryTradingDaysStatement, -1, &queryTradingDaysCPtr, nil) == SQLITE_OK {
        
        while (sqlite3_step(queryTradingDaysCPtr) == SQLITE_ROW) {
            
            let fundName = sqlite3_column_text(queryTradingDaysCPtr, 0)
            let timeStamp = sqlite3_column_text(queryTradingDaysCPtr, 1)
            let close = sqlite3_column_double(queryTradingDaysCPtr, 2)
            
            let fundNameAsString = String(cString: fundName!)
            let timeStampAsString = String(cString: timeStamp!)
            
            let timeStampAsDate = timeStampFormatter.date(from: timeStampAsString)!
            tradingDay.fundName = fundNameAsString
            tradingDay.timeStamp = timeStampAsDate
            tradingDay.close = close
            
            queryResults.append(tradingDay)

        } // end while loop
        
    } else {
        let errorMessage = String(cString: sqlite3_errmsg(db))
        print("\nQuery is not prepared \(errorMessage)")
    }
    sqlite3_finalize(queryTradingDaysCPtr)
    
    return queryResults
}

Answered by ChrisMH in 760387022

Turns out that SQLite supports a case statement. So I incorporated that and strftime into my query to adjust the date back to Friday if the current date is Saturday '6' or Sunday '0'. Works.

    let queryTradingDaysStatement = """
    Select
        FundName,
        TimeStamp,
        Close
    FROM
        TradingDays
    WHERE
        FundName = '\(fundName)'
        AND
            CASE strftime('%w')
                WHEN '0' then TimeStamp >= date('now', '-2 days', 'start of day', '-\(numYears) year')
                WHEN '6' then TimeStamp >= date('now', '-1 days','start of day', '-\(numYears) year')
                ELSE TimeStamp >= date('now', 'start of day', '-\(numYears) year')
            END
    ORDER By
       TimeStamp ASC
    ;
    """

Boost

Answer 1

endecotp OP

Jul ’23

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

1

Answer 2

ChrisMH OP

Jul ’23

Not a perfect solution but I use the SQLite function date and now to get the current days date and subtract one year from that. The ideal solution would for Saturdays and Sundays to always return as the preceding Friday. Will work on that and If I can determine a solution will post it.

    let queryTradingDaysStatement = """
    Select
        FundName,
        TimeStamp,
        Close
    FROM
        TradingDays
    WHERE
        FundName = '\(fundName)'
        AND
        TimeStamp >= date('now', 'start of day', '-1 year')
    ORDER By
       TimeStamp ASC
    ;
    """

0

Answer 3

ChrisMH OP

Jul ’23

Accepted Answer

Turns out that SQLite supports a case statement. So I incorporated that and strftime into my query to adjust the date back to Friday if the current date is Saturday '6' or Sunday '0'. Works.

    let queryTradingDaysStatement = """
    Select
        FundName,
        TimeStamp,
        Close
    FROM
        TradingDays
    WHERE
        FundName = '\(fundName)'
        AND
            CASE strftime('%w')
                WHEN '0' then TimeStamp >= date('now', '-2 days', 'start of day', '-\(numYears) year')
                WHEN '6' then TimeStamp >= date('now', '-1 days','start of day', '-\(numYears) year')
                ELSE TimeStamp >= date('now', 'start of day', '-\(numYears) year')
            END
    ORDER By
       TimeStamp ASC
    ;
    """

0

Answer 4

endecotp OP

Jul ’23

Is there a way to include capturing the latest date as part of the query?

SELECT * FROM table WHERE d > date( (SELECT max(d) FROM table), '-1 year' )

With more complex sub-selects, it might look better if you move it to a WITH clause at the start.

Turns out that SQLite supports a case statement. So I incorporated that and strftime into my query to adjust the date back to Friday if the current date is Saturday '6' or Sunday '0'.

Julian Day Numbers are often useful for calculations like this. It possibly isn't quicker or clearer than what you're currently doing but it's a useful feature to be aware of generally.

1

Answer 5

ChrisMH OP

Jul ’23

Your subquery works but using your suggestion of a With clause appears to me to be the perfect solution. It allows me to get the latest date of available data for a given fund and calculate one year earlier which was my goal. Even better, it is easy to follow. Below is the updated query using a With clause. It works. Thank you.

let queryTradingDaysStatement = """
WITH
TempTable1
AS
(
    SELECT max(TimeStamp) as LatestDate
FROM
    TradingDays
WHERE
    FundName = '\(fundName)'
),

TempTable2
AS (
SELECT
    date(LatestDate, 'start of day', '-\(numYears) year') as StartingDate
FROM
    TempTable1
)

SELECT
    FundName,
    TimeStamp,
    Close
FROM
    TradingDays main, TempTable2 temp
WHERE
    main.FundName = '\(fundName)'
    AND
    main.TimeStamp >= temp.StartingDate
ORDER BY
    main.TimeStamp ASC
;
"""

0