A "Time" Type

[aviks]

It may be useful to have a Time type to represent time of day, without reference to a date. There are various use cases, but my current needs come from wanting to support the corresponding SQL type.

I have a feeling this was discussed earlier, but can't find it. Apologies if I am rehashing old ground.

cc: @quinnj

[quinnj]

There was a julia-users discussion where this came up and I threw this together over a lunch break.
There's also this that I have for SQL-interop in ODBC.jl.

I wouldn't be against having this in Base since it rounds things out and isn't too much work, but I think we've just been waiting to hear how many people really need it.

[ScottPJones]

I've seen a lot of places (in other languages / databases) where the Time type is simply a DateTime with a zero Date part, although that really depends on how DateTime is represented.
The SQLTime in ODBC.jl actually can't handle M$'s SQL time datatype, which takes 3-5 bytes and has up to 7 digits of fractional seconds (100 nanosecond accuracy).
Note: You could store the time in Float64 as nanoseconds (scale the seconds by 10^9), and you wouldn't get any rounding issues, because 86401 * 10^9 will fit in around 48 bits, and operations on Time would be much faster, IMO.

[quinnj]

@ScottPJones, the SQLTime type exists purely as a library wrapper struct and is not meant to serve as a real implementation. It simple matches the corresponding SQLTime struct in sqltypes.h ODBC header file so that when a DBMS returns a "time" type, we can allocate native Julia structs instead of having to return them as strings. I was merely pointing it out because @aviks mentioned SQL compatibility.

[aviks]

+1 to adding that immutable Time code from the mailing list into Base (and Dates.jl). Presumably it should extend one of the base types?

I want my database code to return proper julia values, not a wrapper type. Presumably that's the reason you are accessing the database... to read values into Julia :). So this will be very useful in this case.

[ScottPJones]

@quinnj That's the old format, see the SQL_SS_TIME2 type, which is used to support SQL Server time data types in ODBC.
I propose using a Unsigned (or signed) bitstype in Julia, storing the number of nanoseconds, to represent times, that allow fast calculations, and be able to handle times from any database that I am aware of.

[tbreloff]

Here's a gist with my TimeOfDay implementation... I've considered splitting it off but haven't done it yet:
https://gist.github.com/tbreloff/28b64abd30412add4849

[ScottPJones]

Very nice! That's exactly what I think Julia needs for Time (except I can see you don't use 32-bit platforms any more, you need to change the Int to an Int64 😀!) (I'd like to forget 32-bit platforms myself, at least with the code for my new consulting job, I can!)

[tbreloff]

Heh... yes I certainly forget 32-bit still exist

[quinnj]

@ScottPJones, that type is SQL Server-specific and does not represent the ODBC standard in general or other DBMS systems. It is only available through the SQL Serve Native Client, which you could wrap directly instead of going through the ODBC driver manager, similar to wrapping the MySQL or Postgres native API libraries.

You should also probably take a look at the links I originally replied with which include a Time implementation based on an Int64.

[aviks]

@tbreloff thanks for the pointer, that's cool. As you can imagine, it can't be merged into base as is, due to its dependence on Calendar. @quinnj 's code is effectively a simplified version of yours... i noticed two major differences. One, you use nanoseconds, rather than milliseconds. And two you use timezone offsets. Any other major functionality differences?

I hope something like this gets into Dates soon. @quinnj will you have time to do a PR based on your code any time soon (maybe when you want a change from strings ? :) . Or @tbreloff ? If not I'll do one later in the week.

[ScottPJones]

Ah, shoot, my bad! I missed that (the light blue link colors weren't visible outdoors), and I just looked directly at the copy of ODBC.jl I've got on my laptop, with the Immutable Time there.
Yours is in milliseconds, which fits with the JDBC definition (java.sql.Time), but I think I like having the full nanosecond values, since I know other databases do allow nanosecond time values (even if all ODBC drivers don't support that).

[tbreloff]

Yeah you should certainly ignore the Calendar and other date-related stuff (I just copied/pasted from my codebase). TimeOfDay is very similar to the links @quinnj posted... just figured I'd add what I use on a day to day basis.

I certainly feel like it has to be in nanoseconds (or microseconds at a minimum), otherwise there's a whole class of problems that make it useless. I suppose you could have 2 different types and just convert between them?

[tbreloff]

The other thing to note: TimeOfDay has no implicit concept of location/timezone. It depends on context. All it is is a wrapper around a nanosecond count, with a few helper functions. I'm happy to reduce the gist to the bare minimum if people want me to.

[StefanKarpinski]

+1. I had argued for / requested a Time type for similar reasons – it's natural for interfacing with external data sources that also have a time type. I don't really care for using DateTime instances with zero date components – it wastes memory and is a valid DateTime value, albeit probably an uncommon one. The fact that Tuple{Date,Time} is isomorphic to DateTime also makes me feel like we should have this.

[quinnj]

Yes, the basing on Milliseconds was because that's the most granular Period type we currently have defined in Base and corresponds to the granularity of DateTime. I wouldn't be opposed to having it defined in terms of nanoseconds however, especially if that's going to be much more useful to people.