Support for sqlite date and time function

[cooliceman]

Can you add support for Date and time functions?
These function are very important and convenience to do with dates and times.

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

[stephencelis]

In concert with this extension (you'll want to use the String-backed implementation), you should be able to do the following:

func date(timestring: String, modifiers: String...) -> Expression<NSDate?> {
    if !modifiers.isEmpty {
        let templates = ", ".join([String](count: count(modifiers), repeatedValue: "?"))
        return Expression("date(?, \(templates))", [timestring] + modifiers)
    }
    return Expression("date(?)", timestring)
}

// etc.

I'd still like to have first-class support for this in the future (which means lifting the public embargo on Foundation), so I'll leave this open.

[stephencelis]

For what it's worth, if you use the above extension you may find that your desire to use the SQLite datetime functions goes away entirely. You can usually just use NSDate objects:

// assuming:
let publishedAt = Expression<NSDate?>("publishedAt")

// you can write:
let published = posts.filter(publishedAt <= NSDate())
// SELECT * FROM "posts" WHERE "publishedAt" <= '2015-06-19 07:52:00'

Beyond that, you could use a library like Timepiece to make things prettier:

let recentlyUpdated = posts.filter(1.week.ago..<NSDate() ~= publishedAt)
// … WHERE "publishedAt" BETWEEN '2015-06-12 07:52:00' AND '2015-06-19 07:52:00'

[cooliceman]

Thanks a lot for your reply. Your answers give me a lot of help. Waiting for the support for date and time functions.

[stephencelis]

@cooliceman There should be no reason to wait: you can use them now if you use the extension I pasted in above, or you can use NSDate directly as recommended after.

[cooliceman]

Thanks for your reply.I will use NSDate to do date and time compre and search as you say.

[Serhii-the-Dev]

Sorry, but the proposed solution 1.week.ago..<NSDate() ~= publishedAt causes a compiler error: Binary operator '~=' cannot be applied to operands of type 'HalfOpenInterval' and 'Expression'

[stephencelis]

@C4Grey NSDate must conform to both SQLite.Value and Swift.Comparable. The former requires the extension I linked to earlier. The latter requires Timepiece or similar. If you're still having trouble can you provide all the boilerplate code needed to reproduce?

[Serhii-the-Dev]

@stephencelis Thank you for the reply. Yes, I am using both the extension that you provided(representing a date as a TEXT) and the Timepiece framework. It seems like Timepiece do not implements the pattern match operator(~=) for dates, so I am wondering if the example that you wrote above worked in your case. Anyway, here is the simple test:

import UIKit
import XCTest
import SQLite
import Timepiece

extension NSDate: Value {
    public class var declaredDatatype: String {
        return String.declaredDatatype
    }
    public class func fromDatatypeValue(stringValue: String) -> NSDate {
        return SQLDateFormatter.dateFromString(stringValue)!
    }
    public var datatypeValue: String {
        return SQLDateFormatter.stringFromDate(self)
    }
}

let SQLDateFormatter: NSDateFormatter = {
    let formatter = NSDateFormatter()
    formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSS"
    formatter.locale = NSLocale(localeIdentifier: "en_US_POSIX")
    formatter.timeZone = NSTimeZone(forSecondsFromGMT: 0)
    return formatter
    }()

class SQLiteDateExtensionTest: XCTestCase {
    private let database = Database()
    private var table:Query!
    private let columnTestDate = Expression<NSDate>("test_date")
    private let date = NSDate()

    override func setUp() {
        super.setUp()
        table = database["test"]
        database.create(table: table) { t in
            t.column(columnTestDate)
        }
        database.trace(println)
    }

    func testDate() {
        let insert = table.insert(
            columnTestDate <- date
        )
        XCTAssert(!insert.statement.failed, "Inserted")        
        let rows = Array(table.filter(date.beginningOfDay..<date.endOfDay ~= columnTestDate))
        XCTAssertNotNil(rows.first?.get(columnTestDate), "Should fetch the recently inserted date")
    }

    override func tearDown() {
        database.drop(table: table, ifExists: true)
        super.tearDown()
    }
}

P.S. Also I've figured out that the such request will work fine:

let rows = Array(table.filter(columnTestDate >= date.beginningOfDay && columnTestDate <= date.endOfDay))

[stephencelis]

@C4Grey SQLite.swift provides the pattern matcher here:

SQLite.swift/SQLite/Expression.swift

Lines 532 to 537 in fde8c33

	public func ~= <I: IntervalType, V: Value where V.Datatype: protocol<Binding, Comparable>, V.Datatype == I.Bound>(lhs: I, rhs: Expression<V>) -> Expression<Bool> {
	return Expression(literal: "\(rhs.SQL) BETWEEN ? AND ?", rhs.bindings + [lhs.start, lhs.end])
	}
	public func ~= <I: IntervalType, V: Value where V.Datatype: protocol<Binding, Comparable>, V.Datatype == I.Bound>(lhs: I, rhs: Expression<V?>) -> Expression<Bool?> {
	return Expression<Bool?>(lhs ~= Expression<V>(rhs))
	}

I'll take a look tonight and try to figure out what condition isn't being met.

[cfilipov]

Looks like the code snippet from earlier in this conversation no longer compiles:

func date(timestring: String, modifiers: String...) -> Expression<NSDate?> {
    if !modifiers.isEmpty {
        let templates = ", ".join([String](count: count(modifiers), repeatedValue: "?"))
        return Expression("date(?, \(templates))", [timestring] + modifiers)
    }
    return Expression("date(?)", timestring)
}

Updated to modern swift:

func day(timestring: String, modifiers: String...) -> Expression<NSDate?> {
    if !modifiers.isEmpty {
        let templates = [String](count: modifiers.count, repeatedValue: "?").joinWithSeparator(", ")
        return Expression("date(?, \(templates))", [timestring] + modifiers)
    }
    return Expression("date(?)", timestring)
}

But it's still not compiling. Looks like the constructor for Expression expects a Binding.

Binary operator '+' cannot be applied to operands of type '[Binding?]' and '[String]'

It looks like String was already extended to implement the protocol, so I don't understand why it's not working.

Edit: I wasn't able to get the general date() function working, but for my purposes I needed it for a GROUP BY clause so this worked better:

extension Expression where Datatype: NSDate {
    var day: Expression<NSDate> {
        return Expression<NSDate>("date(\(template))", bindings)
    }
}

With that extension, and assuming you have a column defined like this:

let date = Expression<NSDate>("date")

You can now use it to group rows by day:

table.select(date.day, foo.sum, bar.sum).group(date.day)

Likewise, extensions for month, year etc... can be made as well.

[huntwj]

At the risk of shamelessly bumping this, what is the status here? It seems "native support" for DATETIME columns should be something the library supports. I'm happy to help as I can, but Swift is not my first or second language, so my ability to contribute is less than I'd like.

Thanks in advance for the update.

[jberkel]

@huntwj seems like this ticket got buried, i re-added it to the next milestone. if it's not included in the core lib the provided snippet should at least compile ok.