More date and time types support

[novi]

I have updated this implementation on pull request #93.

Date type in Swift is recommended way to treat typical date and time type TIMESTAMP, DATETIME in MySQL commonly used for creation date, updates date.

Date only supports TIMESTAMP, DATETIME MySQL types.

DateComponents type in Swift is just container for representing SELECT ed string from databases into it.
YEAR, TIME, DATE, TIMESTAMP, DATETIME column can be parsed into DateComponents. And used as query parameters for these types in MySQL as well.

DateComponents doesn't respect or treat for timeZone and calendar on itself by this library. But Date does respect time zone on ConnectionOption.timeZone.

Check more details on its tests and another one.

---

This is original proposal:

We could implement this:

• treat MySQL TIMESTAMP and DATETIME as SQLDate(and NSDate)
• DATE as SQLDate(NSDate) with YYYY-MM-DD 00:00:00
• TIME as String
• YEAR as Int

TIME type has range of '-838:59:59.000000' to '838:59:59.000000’, we couldn’t treat as NSDate. So we support it with String type for now.

(https://dev.mysql.com/doc/refman/5.6/en/datetime.html)

Related #24.

MySQL has 0000-00-00 date value for representing dummy date or empty date, or sometimes null.
We could support such case in this library by following JDBC zeroDateTimeBehavior. (#24)

[tomohisa]

I think this is very good solution. 0000-00-00 Behavior is just giving headache for many developer I am hoping they should just disabled it ;)

[popaaaandrei]

For DATE (YYYY-MM-DD) I have added another case which works for me and solves my use case. @novi Would you like me to make a pull request with this? PS. Also shouldn't we also throw a QueryError.invalidSQLDate(sqlDate) when the cal.date(from :comp) conversion fails?

switch sqlDate.characters.count {
...
case 10:
            // YYYY-MM-DD
            let chars:[Character] = Array(sqlDate.characters)
            if let year = Int(String(chars[0...3])),
                let month = Int(String(chars[5...6])),
                let day = Int(String(chars[8...9])),
                year > 0 && day > 0 && month > 0 {
                var comp = DateComponents()
                comp.year = year
                comp.month = month
                comp.day = day
                comp.hour = 0
                comp.minute = 0
                comp.second = 0
                let cal = SQLDateCalendar.calendar(forTimezone: timeZone)
                if let date = cal.date(from :comp) {
                    self.timeInterval = date.timeIntervalSince1970
                    return
                }
            }

[novi]

Thanks.
It is fine to me. Could you make a pull request?

And after cal.date(from :comp) fails, it exit switch and throws QueryError.invalidSQLDate in L90 . Otherwise(if succeed) set parsed timeinterval to self.timeInterval and returns.
So always throws error if the date parsing fails.

[popaaaandrei]

Sure. Let's recap first, maybe we should add more cases.
Right now there are 3 cases + default:

• YYYY, and gets converted to (year, first month, first day), hour = minute = second = 0
• YYYY-MM-DD (DATE), and gets converted to that day, hour = minute = second = 0
• YYYY-MM-DD HH:MM:SS (DATETIME / TIMESTAMP)
• default: QueryError.invalidSQLDate

Is something missing?
Andrei

[novi]

@popaaaandrei Very thanks.
These behaviours looks good.

[eneko]

Hello there,

Does anyone know if any of these features was implemented? I'm getting errors with DATE and DATETIME fields.

Thanks,
Eneko

[novi]

Hello,

Those features are partially implemented.

DATE will be not handled out of the box.

But we could handle DATE with cast() like:

SELECT cast(cast('2001-01-01' AS DATE) AS DATETIME);

The result will be 2001-01-01 00:00:00 and can be decoded to Date.

[florianreinhart]

@novi Regarding the original proposal for additional date type support: Wouldn't it be better to use (NS)DateComponents for DATETIME, DATE, TIME and YEAR?

(NS)Date represents a fixed point in time, which TIMESTAMP does as well, but DATETIME (and the other types) don't. They are instead stored as a formatted date string.

[novi]

@florianreinhart Thanks for the suggestion. DateComponents for date types in MySQL seems working well.

I have added implementation for DateComponents on #93 and updated the proposal.