Finalize Date type

[asfimport]

Parquet defines it as: "number of days from the Unix epoch, 1 January 1970."
https://github.com/apache/parquet-format/blob/master/LogicalTypes.md#datetime-types

We should make it the same.

Reporter: Julien Le Dem / @julienledem
Assignee: Wes McKinney / @wesm

Related issues:

• Add integration tests for date and time types (blocks)
• [Format] Days since epoch date type (relates to)

_{Note: This issue was originally created as ARROW-316. Please see the migration documentation for further details.}

[asfimport]

Julien Le Dem / @julienledem:
java implementation looks wrong:

arrow/java/vector/src/main/codegen/templates/FixedValueVectors.java

Line 496 in c3930a0

return date;

[asfimport]

Wes McKinney / @wesm:
So in the end we are using int64 milliseconds since the UNIX epoch – we do have a date32 type in the C++ implementation since days-since-UNIX-epoch occurs frequently in the wild. We can document this in the format/ section to close this issue, anything else to discuss?

[asfimport]

Julien Le Dem / @julienledem:
What representations do we need?

• millisecond since the UNIX epoch on 64 bits: does this mean the timestamp at midnight? This is not the same as number of days * 24 * 3600 * 1000 because of leap seconds.
• number of days since the UNIX epoch on int32. This one seems straightforward.

Do we want to make the Date type more explicit similar to the current Time type discussion? Meaning it has a unit and a bitWidth? This seems overkill since the precision is always day.

[asfimport]

Wes McKinney / @wesm:
@julienledem I guess we should revise the description to be "UNIX time", which does not include leap seconds (https://en.wikipedia.org/wiki/Unix_time).

One option is to add an enum like:

enum DateUnit: short { 
  DAY,   /// 32-bit storage
  MILLISECOND,   /// 64-bit storage
}

and add that to the Date flatbuffer table.

[asfimport]

Wes McKinney / @wesm:
In the interest of an expedient resolution, I opened PR #390 for discussion.

As an extra data point, the DateUnit::DAY 32-bit representation is the one used by PostgreSQL.

[asfimport]

Julien Le Dem / @julienledem:
Issue resolved by pull request 390
#390

[asfimport]

Luke Higgins:
sure you don't want to account for leap seconds? On CentOS7 for example unix time from date command does give current UTC (which is adjusted for leap seconds). If I am timestamping events on my unix host and storing them the timestamp will most likely be UTC (modulo timezone locale perhaps)

[asfimport]

Wes McKinney / @wesm:
[~virtualluke] do you have any documentation about that? The date command returns the current system time, which does account for leap seconds. When there is a leap second, the same second occurs twice https://access.redhat.com/articles/15145. As far as the actual UNIX timestamps, my understanding is that they do not include leap seconds.

[asfimport]

Luke Higgins:
I was wrong, read some documentation on the date command incorrectly. Looks like storing without leap seconds is pretty universal.