Proposal: Dates

[mlucy]

I propose that we introduce three new pseudo-types: dates (by which I mean date, time, and optional timezone), durations, and intervals, all cribbed from ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601), which we represent as strings.

We introduce the following terms:

date.date_sub(other_date) => duration
date.dur_sub(duration) => other_date
date.dur_add(duration) => other_date
duration.dur_sub(other_duration) => third_duration
duration.dur_add(other_duration) => third_duration

date.date_{lt/gt/le/ge/eq/ne}(other_date) => bool
duration.dur_{lt/gt/le/ge/eq/ne}(other_date) => bool

# intervals can also be specified directly as strings
r.interval(date, other_date) => interval
r.interval(date, duration) => interval
date.during(interval) => bool

We should also update the drivers to automatically render native dates/durations/intervals as the appropriate strings.

So, for example, in Ruby you could get all rows inserted in the last day with:

table.between(Date.today, Date.today - 1, :index => :timestamp)

Or, if Ruby didn't have easy date manipulation:

table.between(Time.now, r(Time.now).dur_sub("P1M"), :index => :timestamp)

[AtnNn]

Can we spend some more time on this and add separate data types?

[jdoliner]

@AtnNn I'm in your boat on this one but let's wait until we have the discussion phase.

[coffeemug]

Moving back to backlog. Nested objects are more important.

[Linicks]

Having built in Date types and tools would be a major +1 :)

[perone]

+1

[hcarvalhoalves]

Why reimplementing date manipulation primitives would be necessary? Shouldn't suffice to have queries take native date types and have the driver convert to a wire format?

[coffeemug]

@hcarvalhoalves suppose you wanted a query that says something like "Give me every event that happened on a Monday". In order for that to work on the server side, it would need a db-level function that determines the day of the date, otherwise you'd have to transfer all the data to the client first and do the processing on the client.

[hcarvalhoalves]

@coffeemug Right, I get what you mean now. I just didn't get where the above methods fit in your example though. I expected date manipulation would look something like this:

r.table('events').filter(
  r.row('takes_place_in').weekday().eq(1) # Monday
).run(conn, callback)

No?

Sorry if my questions are stupid, I'm still figuring out RethinkDB, I'm not sure what's possible or not.

[coffeemug]

Yep, this is exactly right. Barring some syntactic debate, this is exactly how we'd do it, I think.

[AtnNn]

I would like to propose a few modifications and additions to @mlucy's proposal.

I'm going to call these objects times instead of dates.

In the iso8601 representation, the time component should be optional as well as the time zone component.

The P and T prefixes are optional for durations. One can write "1M" or "1:30" instead of "P1M" and "PT1:30". Durations can also be represented simply as a number of seconds.

All operations assume that all days have exactly 86400 seconds. "23:60" is considered equal to "23:59".

r.run() takes an additional time_zone argument that specifies the default time zone used in the query. The client drivers automatically set the time_zone to the local time zone of the client. The default time zone is only used where it is explicitly mentioned in this proposal. If time_zone is set to the empty string, time zone defaulting is disabled and all operation that would require using the default time zone fail.

---

time.time_sub(other_time) => duration
time.time_{lt/gt/le/ge/eq/ne}(other_time) => bool

When only one of time or other_time has a time zone, the other value is considered to be in the default time zone. If both time and other_time have no time zone, neither does the result.

time.dur_sub(duration) => other_time
time.dur_add(duration) => other_time
duration.dur_{lt/gt/le/ge/eq/ne}(other_duration) => bool
duration.dur_sub(other_duration) => third_duration
duration.dur_add(other_duration) => third_duration

time.during(begin_time, end_time) => true
time.during(begin_time, duration) => true
time.during(duration, end_time) => true

There are no intervals. during simply takes two arguments.

Times default to the default time zone if the time zone component is missing, unless all times have no time zones.

time.date() => time  # the date component
time.time_of_day() => duration  # the time component
time.time_zone() => string  # time zone component
time.year() => number
time.month() => number
time.day() => number
time.week_day() => number  # 0 = Sunday, 1 = Monday, ..., 6=Saturday
time.year_day() => number  # 1-366
time.hour() => number
time.minute() => number
time.seconds() => number  # floating point
duration.totalSeconds() => number

If the time is not present, the corresponding accessors return null. Ditto for the time zone.

r.time(year, month, day, hour, minute, seconds, tz) => time
r.time(year, month, day, hour, minute, seconds) => time
r.time(year, month, day) => time

A constructor.

r.sunday = 0
r.monday = 1
...
r.saturday = 6
r.january = 1
...
r.december = 12

time.epoch_time() => number

Equivalent to time.time_sub("1970-01-01T00:00Z").totalSeconds()

r.now() => string
r.now(tz) => string

The current time in the tz time zone. If tz is not specified, use the default time zone. If the default time zone is not specified, use "Z".

time.format_time(format) => string
string.parse_time(format) => time

Format and parse times like strftime and strptime.

time.with_time_zone() => time
time.with_time_zone(tz) => time
time.set_time_zone(tz) => time

with_time_zone returns the same time in a different time zone. If the time has no time zone, it converts from the default time zone. With no arguments, it converts to the default time zone.

set_time_zone simply changes the time zone component.

---

Instead of using iso8601 string representation, times could instead be represented as a number of seconds since epoch and durations as a number of seconds. There are a few advantages:

• using regular comparison and arithmetic operators instead of time_* and dur_*
• using existing r.between instead of adding a new r.during
• faster: no need to parse and format the time for each operation

There are also some disadvantages:

• debugging is harder with seemingly opaque numbers rather than with nicely formatted strings
• time zone information is lost, all times would be stored as UTC
• representing a local time (with no time zone) is impossible

A better representation would be a new native type instead of a pseudo-type. This would have all these advantages and none of these disadvantages.

[coffeemug]

FYI, this issue looks like the only one in 1.8-required that's non-trivial and wide open (since we didn't discuss it much). Let's try to hammer this out. (P.S. my feedback on the specific proposal coming soon)

[mlucy]

The P and T prefixes are optional for durations. One can write "1M" or "1:30" instead of "P1M" and "PT1:30". Durations can also be represented simply as a number of seconds.

This is ambiguous. In ISO8610 P1M means one month and PT1M means one minute.

"23:60" is considered equal to "23:59".

What's the motivation for this? That sound wacky.

duration.totalSeconds() => number

Could we call this dur_seconds for consistency?

r.sunday = 0
r.january = 1

Why is sunday numbered from 0 when january is numbered from 1?

time.epoch_time() => number

This name sounds slightly off to me. See below for discussion of how I think we should handle ISO8601 vs. epoch time.

Format and parse times like strftime and strptime.

This seems strange to have in the server. Shouldn't people be doing this in the clients once they've retrieved the date? Also, we'd be violating the standard unless we want the clients to send the whole locale to the server.

with_time_zone

Could we call this in_time_zone? It seems less ambiguous to me.

---

Instead of using iso8601 string representation, times could instead be represented as a number of seconds since epoch and durations as a number of seconds.

I think this is the biggest thing left to decide. Here's what I think we should do (although I need to think about it more):

• Store everything internally as seconds since epoch. People know how to deal with this, it's easy, and it always sorts correctly. Use negative numbers for dates before 1970.
• Have the functions dur_to_iso8601 and time_to_iso8601 for when people want to do regexp matching or something. Have the functions dur_to_epoch and time_to_epoch for when people have an ISO8601 string that they want in seconds. (Maybe with better names?)
• Make all the functions (date_add, dur_sub, etc.) polymorphic, and let them accept either numbers (which are interpreted as seconds) or ISO8601 strings, but have them always return seconds since epoch.

[coffeemug]

Sorry to complicate matters, but I must. First, a few random thoughts:

• I think that if we implement things like time_sub, instead of adding a proper time type and making sub polymorphic, we will very quickly grow to regret this decision.
• I also think that on the driver side people should be able to operate with standard time/date types. E.g. r.expr(datetime.now()) should work in Python. They should be able to say table.insert({time: datetime.now()}), and get a native Python datetime object if they get the row back IMO. (at the very least, we should consider this carefully)

I propose the following:

• Store time object like this on the server: { __reql_type__: 'time', ms_since_epoch_utc: X }
• Whenever possible, use existing operations for math and logic (i.e. make add, gt, etc. polymorphic on time)
• In the drivers, convert __reql_type__: 'time' back to native object (e.g. datetime in Python, Date in JS, etc.)
• On the server, implement all reasonable time functions (e.g. things listed here: http://www.tutorialspoint.com/javascript/javascript_date_object.htm -- stuff like getYear and setYear)
• I think the server shouldn't ever store timezone information, just milliseconds since epoch in UTC. When the user says something like r.row('time_field').getDay.eq('Monday') they either pass the timezone they want via run (which the drivers would default to local timezone), or the server assumes they're speaking in UTC if the driver passes nothing.
• The user can translate things to string representations on the server and store it in attributes, but those are strings, not times. They can't run time ops on it unless they call r.parse_time.

[mlucy]

So, in this one particular case the __rql_type__ solution works fairly well, because our naive ordering on objects exactly matches the ordering we want (since _ is less than all letters and ms_since_epoch_utc will sort correctly).

So I wouldn't be incredibly opposed to doing it that way. The only large cost I can think of is that people could no longer use times as primary keys.

What do you think, @AtnNn ?

[jdoliner]

@coffeemug's proposal is actually almost exactly what I was in the process of writing. The one thing I wonder about is if we could even get away without have getYear functions and instead having a syntax like r.expr(datetime.now)["year"] which would basically amount to a way to define methods on custom types. This would save us having to add 10 different protocol buffer terms to the language which I'm really not crazy about and would also be a syntax that we could eventually expose to people using register_type I actually think it could be a really powerful feature.

Also the issue about ordering is pretty moot IMO we should extend our ordering rules so they order custom types by their typenames rather than just considering them all to be objects. We can also allow for custom orderings of the different members of the type (although in this case the ordering we want happens to be the same as the one for objects.

Not being able to have dates as keys is a pretty big deal breaker IMO opinion. We should just allow objects as keys there's really no good reason not to do that when we allow arrays as keys.

[mlucy]

I think that having people access non-existent attributes of objects as a metaphor for function calls might be a little confusing. Also, it would be confusing for o[:year] to work but not o.pluck(:year).

Also, do we really allow arrays as primary keys?

If we switch to supporting objects as primary keys, we'd have to go back and make the optional argument to get_all mandatory in order to remove any ambiguity.

[ekanna]

May be you can borrow some ideas from golang time package as well. It is looking nice.
http://golang.org/pkg/time/

[jdoliner]

Yeah accessing attributes for methods is not quite there. I really wish there was some way to make it work though.

Arrays right now can't be pkeys but the only reason for that is space concerns and we can fix that fairly easily. Using times as keys in a secondary index is going to be really important to people though. I think making the optional argument to get_all not optional is not a good enough reason not to allow objects as keys. It will be really nice if a person can just register a type and use it was a primary key.

[mlucy]

I wasn't saying we shouldn't make the optarg non-optional, I was saying that we should when/if we change the rules for primary keys. I just didn't want us to forget about it.

[coffeemug]

In response to some comments above:

having a syntax like r.expr(datetime.now)["year"]

I don't like that for the same reasons @mlucy already mentioned

Not being able to have dates as keys is a pretty big deal breaker IMO opinion

Agreed, this feature would be next to useless without indexing

---

I thought about this some more and realized that there are 5 questions we need to answer, and while they're not all necessarily completely orthogonal, I think it's worth thinking about them independently:

1. How to represent time on the server wrt to functionality (e.g. seconds since epoch, iso8601, etc.)
2. How to represent time on the server wrt to our architecture (e.g. just a string/number, object that contains __reql_type__ etc.)
3. How to present time creation/manipulation to the client driver (e.g. use client language time time objects, return iso8601 representation, etc.)
4. Which time access functions to implement on the server.
5. How to deal with time zones.

After thinking a bit more, here's what I now think about each one of these:

1. Milliseconds since epoch in UTC seems like the most machine-friendly, compact, sortable representation there is. Unless I'm missing smtg, I see no advantage in using an alternative representation from a functionality perspective.
2. As far as our architecture, I have a few thoughts
   • It's a bit more work but is also strictly superior to define a type the server knows about (because I think we want to make commands like sub and gt work on time). From the server's perspective, it doesn't have to be an object with __reql_type__ field -- we can create a new Datum type, which probably would be a more sensible representation. I think we should pick a representation that suits our internal needs best.
   • For any Datum that doesn't natively map to JSON, I think it's important to have two well defined functions: to_json and from_json. We could then use things like __reql_type__ to fall back to the json representation if we need to (or parse it from clients that don't wan to do custom datums or smtg), but I don't think we should be storing it this way internally.
3. I think that whether we choose to convert to native language date/time type, or return an object with __reql_type__ and let users fend for themselves, or whatever, we'll be ok. I would work out the other aspects first, and deal with this last.
4. I think we can agree on this once the other stuff is ironed out.
5. I think we're mostly in agreement here (have the driver pass local timezone to run by default, use UTC if it's missing, but always convert to UTC on the server).

[coffeemug]

Actually another open question (6) that's important is which types to implement. E.g.: Date, Time, DateTime, Duration, etc. (I think we need to support all of these, though Date and Time are merely cases of DateTime with some control bits flipped).

Also FYI -- if you look at MySQL docs (http://dev.mysql.com/doc/refman/5.7/en/datetime.html) they offer a variety of types that handle relevant tradeoffs above differently (e.g. different representations on the server), and I think it's an overkill and is a poor design -- I bet having to choose between DATETIME and TIMESTAMP doesn't make users happy, and having a DATE type, DATETIME type, but no TIME type confuses the heck out of people.

[wmrowan]

Implementing a native date type seems like the right solution to me. We've talked about supporting more types for a long time and, as @AtnNn mentioned above, now seems like as good time to finally address this question. However we decided the date type question now will influence how we solve the integer question etc.

For any custom type, I'd say that there are at least three different representation choices that have to be made: on disk format, in memory format during processing, and the wire format as defined by the protobuf spec that driver developers (and not necessarily users) will see. These can all be distinct. A new datum type in the wire spec gives us the most freedom with the first two by hiding implementation details behind the third. The drivers then serve as another buffer between this format and the values manipulated by user programs, giving some flexibility there as well.

The right way for drivers to handle this type is almost certainly to convert it to a native date type in the host language (or perhaps as a user facing class with host language friendly behavior). Users who wish to have a value that can be serialized as JSON can then make that decision for themselves according to the rules of their host language. The default node behavior if I call JSON.stringify(new Date()) is to return a string encoded in ISO format for example.

This seems much better than leaking a representation like {"__reql_type__": ...} which limits both our's and driver developer's freedom to provide a more natural representation.

[jdoliner]

From a backend perspective I would strongly prefer this to be of the form {__reql_type__ : "date", ...} rather than adding a native type. Since we're going to have at least a datetime and a duration type and possibly also a date and time type this is actually a big increase in the number of native types which is definitely going to make our reql code less nice. I also see no real benefit to it we can easily make the operators we want such as < and - work on such types. I really don't understand @wmrowan's concern about adding in flexibility for the different formats what are we going to be using this flexibility to do.

Another thing to consider is that if dates are represented as native datatypes then we either need a json representation of them or we can't encode rows which contain dates as json and they'll be a lot less efficient. We could of course have a json representation in addition to the native one but since that's what most drivers would be sending and receiving anyways it then seems pretty superfluous to have the native representation.

[neumino]

Could we have sub/add/gt/ge/lt/le/eq/ne polymorphic on date instead of adding new methods?
mul/div on duration would be nice too (if we add such type).

[jdoliner]

@neumino yeah I think this type of polymorphism is basically accepted by everyone at this point.

[coffeemug]

From a backend perspective I would strongly prefer this to be of the form {__reql_type__ : "date", ...} rather than adding a native type [...] I also see no real benefit to it

One benefit is storage efficiency. Storing a number with a few bits in front of it denoting the type is significantly more efficient than storing an object with string keys and string type description. Processing would probably be more efficient too since checking whether the datum is of a given type would likely be more efficient.

If it's actually easiest to store things in the server this way, I'm not opposed to doing it. I just think that our representation choice on the server shouldn't be constrained by clients or json because these issues are completely orthogonal. We can store things on the server in any way we want, and always send them to the clients via the __reql_type__ trick.

[wmrowan]

@jdoliner My point about flexibility is that we can actually choose different representations for the date type for each of four or so different stages. The on disk format doesn't need to be the same as the in memory format used by the query processing layer or the wire format consumed by the drivers or the format consumed by the end user. Given this independence, we're free to pursue different goals for each. We're also free to change the on disk format later without impacting driver developers or end users, or change the in-memory format without changing the on disk format. Since we will inevitably get this wrong now this flexibility will come in handy.

Regardless of how we represent dates on the server I would prefer to hand dates to the driver using a separate datum type to the protobuf definition. This gives driver developers the most flexibility to easily convert the date type to whatever format makes most sense in the host language. If this means construction dictionaries like {'__rethinkdb_custom_type___date_with_time_zone_data': true, '__timestamp_fortnights_since_trinity_test__':7819, '__server_default_timezone': 'UTC'} then better that decision be made by the driver or user than by the server. However we do it internally, I'd really like to avoid leaking a representation like {'__rql_type__: ... to users or even driver developers since it's harder to work with and will inevitably be leaked to users. Making a hack like this part of the public ReQL specification limits or freedom going forward.

[jdoliner]

I really don't see this as a hack. If the language doesn't contain native types to represent the values I think returning an object like {'__reql_type__: ...}` is a very good option. It guarantees that driver developers will always have a standard way to represent types that don't have native representations.

[wmrowan]

I can look into this. It might be tricky though. It depends on how we convert the cJSON objects for consumption by v8 which I'm not immediately familiar with.

[sandstrom]

Date support would be a great addition, really looking forward to it!

However, the proposed syntax makes me think of MongoDB and their inconsistent api which I'm not a big fan of.

Trailing underscores and abbreviations are hard to remember:

duration.dur_{lt/gt/le/ge/eq/ne}(other_duration) => bool
duration.dur_sub(other_duration) => third_duration
duration.dur_add(other_duration) => third_duration

Perhaps you could go with duration.add (or duration.add_duration) and duration.compare instead?

Mixing of camelCase and snake_case is very confusing:

time.year_day() => number
duration.totalSeconds() => number

It would be great if you picked one and used it consistently.

[jdoliner]

Hi @sandstrom the sentiments you have here are definitely shared by the development team and I think everything you have an issue with is not making it into the final product. It's tough to tell because this is such a monstrous thread but we decided pretty early on in the conversation not to have dur_lt, dur_add etc. Durations are actually just numbers now so we you just use normal + and - with them. Furthermore dates have overloads for - and other functions that make sense.

As far as I know every driver we support adheres strictly to either camel case or snake case. Whichever is more standard in the language itself (JS is camel case, python and ruby are snake case). However because we on the dev team all maintain and use different drivers we all wind up with our own preferences for how to talk about things which can be confusing in issues.

[sandstrom]

That's comforting! I've been impressed by the attention to detail that I've seen so far, so I'm glad to hear that the api won't suddenly disintegrate when adding date support :)

⛵

[neumino]

The python driver depends on the package pytz now and importing the driver fails if the package is not installed

>>> import rethinkdb as r
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "rethinkdb/__init__.py", line 4, in <module>
    from query import js, json, error, do, row, table, db, db_create, db_drop, db_list, table_create, table_drop, table_list, branch, count, sum, avg, asc, desc, eq, ne, le, ge, lt, gt, any, all, add, sub, mul, div, mod, type_of, info, time, monday, tuesday, wednesday, thursday, friday, saturday, sunday, january, february, march, april, may, june, july, august, september, october, november, december, iso8601, epoch_time, now
  File "rethinkdb/query.py", line 4, in <module>
    import pytz
ImportError: No module named pytz

[AtnNn]

Should the python driver be using the pytz package? This proposal only supports timezones as UTC offsets, which shouldn't require the Olson database.

[wmrowan]

I might have left an unused import in there but it shouldn't actually be using the pytz package in any way. Currently the polyglot tests do require the package but it's not required to use the driver.

[wmrowan]

Yup, it was an unused import. I've removed it if you want to keep playing with date support.

[neumino]

Hum, I know that it's late but could seconds return an integer?

And we could add a milliseconds later if people want more precision?

It would be nice if people could easily build a string out of a date object. Something like that.

// HH:mm:ss
// Now returns HH:mm:ss.sssssssssssssss
r.now().hours().coerceTo('string').add(':').add(r.now().minutes().coerceTo('string')).add(':').add(r.now().seconds().coerceTo('string'))

Using a regex is a workaround, but that sounds like a lot of work for a common operation.

[coffeemug]

I think not being able to get the milliseconds out is pretty bad. I'd rather have seconds return fractions.

[neumino]

It would be fine if we have ceil/floor I guess -- Related to #866

[neumino]

@wmrowan, time.to_iso8601() is implemented in Python and Ruby but not in JavaScript.

[wmrowan]

@neumino time.toISO8601()

[neumino]

Err, I should have ack with insensitive case. Thanks @wmrowan

[wmrowan]

@neumino I think implementing floor is a better solution to your problem then truncating the output of seconds adding milliseconds.

[neumino]

The review for docs has been completed and merge in dates.

[mlucy]

This is finally in next.

[jdoliner]

Commit hash?

On Thu, Aug 8, 2013 at 4:30 PM, Michael Lucy notifications@github.comwrote:

This is finally in next.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/977#issuecomment-22366007
.

[coffeemug]

Slow clap

[mlucy]

f8ae1a8

[coffeemug]

Ok, so @mglukhovsky just pointed out that slow clap is usually a sarcastic clap. Let's write this one off to cultural differences :)

[mglukhovsky]

🎆 Can't wait to play with this!

[jdoliner]

I think the comedy of errors that made up this issue's circumstances might justify the slow clap here by american standards.

[neumino]

r.db('test').table('posts').filter(function(post) {
  return post("date").day().eq(r.monday)
})

That's pretty cool :)

I suggest now that we should have a flag "lang" in the driver, so I can use r.lundi instead of r.monday etc.

[wmrowan]

@neumino that wouldn't be very clean. We should host a francophone version of ReQL on a different port and make sure that all user facing text (including server generated error messages) is written in idiomatic French.

[AtnNn]

Before we can support French and other languages that are not ASCII we need to fix our string encoding (#1181)