Convert SQLite Julian date to a specific date string format for display

Question

I have a function that queries an SQLite database and returns names, dates and values. In SQLite the dates are in Julian format. I convert the dates in the SQLite query to a date time string which give me "2022-08-01 00:00:00". I want to display this date as the date string "Aug 1, 2022". The only way I have been able to achieve this is to convert the first date string to a date via a date formatter then convert this date to the desired date string via a second date formatter. Is there a more direct way to do this?

func AccessSQLiteDB(db: OpaquePointer?) {

    let queryTradingDaysStatement = """

    WITH
    TempTable1
    AS
    (
    SELECT
        max(TimeStamp) - 365.25 as StartingDate
    FROM
        TradingDays
    WHERE
        FundName = 'Fund1'
    ),
    
    TempTable2
    AS
    (
    SELECT
        main.FundName,
        datetime(main.TimeStamp) as SQLDateString,
        main.Close
    FROM
        TradingDays main, TempTable1 as temp
    WHERE
        main.FundName = 'Fund1'
        AND
        main.TimeStamp >= temp.StartingDate
    )
    
    SELECT
        FundName,
        SQLDateString,
        Close
    FROM
        TempTable2
    ORDER By
        SQLDateString ASC
    ;
    """

    let sqlDateStringFormatter = DateFormatter()
    sqlDateStringFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
    sqlDateStringFormatter.timeZone = .gmt
    
    var queryTradingDaysCPtr: OpaquePointer?
    
    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!)
            print(timeStampAsString)                   // returns this format 2022-08-01 00:00:00
            
            let timeStampAsDate: Date = sqlDateStringFormatter.date(from: timeStampAsString)!
            print("\(timeStampAsDate)")                // returns this format 2022-08-01 00:00:00 +0000
            
            let mediumDataFormatter = DateFormatter()
            mediumDataFormatter.dateStyle = .medium
            mediumDataFormatter.timeZone = .gmt
            
            let dateString = mediumDataFormatter.string(from: timeStampAsDate)
            print(dateString)                          //  returns this format Aug 1, 2022
            
            let closeAsString = String(format: "$%.2f", close)
            
            print(fundNameAsString + " - " + dateString + " - " + closeAsString)
            
        } // end while loop
        
    } else {
        let errorMessage = String(cString: sqlite3_errmsg(db))
        print("\nQuery is not prepared \(errorMessage)")
    }
    sqlite3_finalize(queryTradingDaysCPtr)
}

633

Posted by

ChrisMH

Reply

Add a Comment

Answer 1

Is there a more direct way to do this?

Definitely. Currently it’s doing all this:

calling datetime() within SQLite to convert a stored Julian value to a string;
using one DateFormatter to parse that string into a Date value;
using a second DateFormatter to convert that Date value back to a string.

You can eliminate 1 and 2. Just project the raw Julian value in your query (fetching it via sqlite3_column_double) and then construct your Date value directly using init(timeIntervalSinceReferenceDate:). It just requires simple math to convert from Julian (days since 4713 BC) to Apple reference date (seconds since 2001).

Also this will help your ORDER BY clause a little, since now it will be sorting by a number rather than a string.

And (basic “code review” comment) you can construct mediumDateFormatter just once, before the loop. No need to do it inside the loop.

Posted by

Scott

Add a Comment

Answer 2

Is there a more direct way to do this?

Definitely. Currently it’s doing all this:

calling datetime() within SQLite to convert a stored Julian value to a string;
using one DateFormatter to parse that string into a Date value;
using a second DateFormatter to convert that Date value back to a string.

You can eliminate 1 and 2. Just project the raw Julian value in your query (fetching it via sqlite3_column_double) and then construct your Date value directly using init(timeIntervalSinceReferenceDate:). It just requires simple math to convert from Julian (days since 4713 BC) to Apple reference date (seconds since 2001).

Also this will help your ORDER BY clause a little, since now it will be sorting by a number rather than a string.

And (basic “code review” comment) you can construct mediumDateFormatter just once, before the loop. No need to do it inside the loop.

Posted by

Scott

Add a Comment

Answer 3

Great solution. It makes the query simpler and as you indicated, only requires some simple math to convert between Julian Days and Apples seconds. And yes you are absolutely right that it was a mistake for me to declare a date formatter in a loop. Below is the updated code.

func AccessSQLiteData(db: OpaquePointer?) {

    let queryTradingDaysStatement = """

    WITH
    TempTable1
    AS
    (
    SELECT
        max(TimeStamp) - 365.25 as StartingDate
    FROM
        TradingDays
    WHERE
        FundName = 'Fund1'
    )
    
    SELECT
        main.FundName,
        main.TimeStamp,
        main.Close
    FROM
        TradingDays as main, TempTable1 as temp
    WHERE
        main.FundName = 'Fund1'
        AND
        main.TimeStamp >= temp.StartingDate
    ORDER By
        main.TimeStamp ASC
    ;
    """

    let dateFormatter = DateFormatter()
    dateFormatter.dateStyle = .medium
    dateFormatter.timeZone = .gmt
    
    let daysBetween4713And2001: Double = 2451910.500000
    let secondsPerDay: Double = 86400.00
    
    var queryTradingDaysCPtr: OpaquePointer?
    
    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 daysSince4713BC = sqlite3_column_double(queryTradingDaysCPtr, 1)
            let close = sqlite3_column_double(queryTradingDaysCPtr, 2)
            
            let fundNameAsString = String(cString: fundName!)
            let daysSinceJanOne2001 = daysSince4713BC - daysBetween4713And2001
            let secondsSinceJanOne2001 = daysSinceJanOne2001 * secondsPerDay
            let timeStamp = Date(timeIntervalSinceReferenceDate: secondsSinceJanOne2001)
            let formattedTimeStamp = dateFormatter.string(from: timeStamp)
            let closeAsString = String(format: "$%.2f", close)
            
            print(fundNameAsString + " - " + formattedTimeStamp + " - " + closeAsString)
        } // end while loop
        
    } else {
        let errorMessage = String(cString: sqlite3_errmsg(db))
        print("\nQuery is not prepared \(errorMessage)")
    }
    sqlite3_finalize(queryTradingDaysCPtr)
}

Posted by

ChrisMH

Add a Comment

Convert SQLite Julian date to a specific date string format for display

Accepted Reply

Replies