Hawq follows postgres 8.4
convention, according to which 2000-01-01 acts as a zero value and timestamp
data is stored as 8-Byte signed integer with microsecond precision. From the postgres 8.4 documentation https://www.postgresql.org/docs/8.4/static/datatype-datetime.html “When timestamp values
are stored as eight-byte integers (currently the default), microsecond
precision is available over the full range of values. ... .timestamp values
are stored as seconds before or after midnight 2000-01-01.” So the Hawq Date has a 30
Years offset against Unix epoch (1970-01-01). The
below e.g. function changes the startdateofweek from dataframe back to date. df.select((($"startdateofweek" +
946684800000000L + 14400000000L)/1000000).cast(TimestampType)).show() 946684800000000L
is for 30 Year offset in microseconds. 14400000000L is
for 4 hours EST offset to GMT in microseconds. .
... View more
