Need LOCALTIMESTAMP ?
Hello,
Please help me to understand what I am going to implement for Timestamp. Do we
need LOCALTIMESTAMP implementation? See the comparisons below::
=
LOCALTIMESTAMP
It's often important to get the value of current date and time. Below are the
functions used to do that in the different implementations.
Standard The current timestamp (without time zone) is retrieved with the
LOCALTIMESTAMP function which may be used as:
SELECT LOCALTIMESTAMP ...
or
SELECT LOCALTIMESTAMP(precision) ...
Note that SELECT LOCALTIMESTAMP() ... is illegal: If you don't care about the
precision, then you must not use any parenthesis.
If the DBMS supports the non-core time zone features (feature ID F411), then it
must also provide the functions CURRENT_TIMESTAMP and
CURRENT_TIMESTAMP(precision) which return a value of type TIMESTAMP WITH TIME
ZONE. If it doesn't support time zones, then the DBMS must not provide a
CURRENT_TIMESTAMP function.
PostgreSQL Follows the standard.
Documentation
DB2 Doesn't have the LOCALTIMESTAMP function.
Instead, it provides a special, magic value ('special register' in IBM
language), CURRENT_TIMESTAMP (alias to 'CURRENT TIMESTAMP') which may be used
as though it were a function without arguments. However, since DB2 doesn't
provide TIMESTAMP WITH TIME ZONE support, the availability of CURRENT_TIMESTAMP
could be said to be against the standard—at least confusing.
Documentation
MSSQL Doesn't have the LOCALTIMESTAMP function.
Instead, it has CURRENT_TIMESTAMP which—however—doesn't return a value of
TIMESTAMP WITH TIME ZONE, but rather a value of MSSQL's DATETIME type (which
doesn't contain time zone information).
Documentation
MySQL Follows the standard.
Documentation
Oracle Follows the standard.
Informix On my TODO.
Thanks,
shyam_sar...@yahoo.com
RE: Need LOCALTIMESTAMP ?
Hi Shyam,
I think HIVE-192 is about the fact that there is no support for the timestamp
type in Hive (or for that matter date and datetime types). In FB we are using
strings to hold this information.
If you are planning to add a built in function like localtimestamp, then that
should probably go into a different JIRA.
We have tried to adhere to mysql way of doing things as we find more folks
using it (at least in our company) and looks from your research that they are
basically standards compliant. So my vote will be to go with mysql semantics
and CURRENT_TIMESTAMP construct.
Ashish
-Original Message-
From: Shyam Sarkar [mailto:shyam_sar...@yahoo.com]
Sent: Wednesday, February 11, 2009 2:37 PM
To: hive-dev@hadoop.apache.org
Subject: Need LOCALTIMESTAMP ?
Hello,
Please help me to understand what I am going to implement for Timestamp. Do we
need LOCALTIMESTAMP implementation? See the comparisons below::
=
LOCALTIMESTAMP
It's often important to get the value of current date and time. Below are the
functions used to do that in the different implementations.
Standard The current timestamp (without time zone) is retrieved with the
LOCALTIMESTAMP function which may be used as:
SELECT LOCALTIMESTAMP ...
or
SELECT LOCALTIMESTAMP(precision) ...
Note that SELECT LOCALTIMESTAMP() ... is illegal: If you don't care about the
precision, then you must not use any parenthesis.
If the DBMS supports the non-core time zone features (feature ID F411), then it
must also provide the functions CURRENT_TIMESTAMP and
CURRENT_TIMESTAMP(precision) which return a value of type TIMESTAMP WITH TIME
ZONE. If it doesn't support time zones, then the DBMS must not provide a
CURRENT_TIMESTAMP function.
PostgreSQL Follows the standard.
Documentation
DB2 Doesn't have the LOCALTIMESTAMP function.
Instead, it provides a special, magic value ('special register' in IBM
language), CURRENT_TIMESTAMP (alias to 'CURRENT TIMESTAMP') which may be used
as though it were a function without arguments. However, since DB2 doesn't
provide TIMESTAMP WITH TIME ZONE support, the availability of CURRENT_TIMESTAMP
could be said to be against the standard-at least confusing.
Documentation
MSSQL Doesn't have the LOCALTIMESTAMP function.
Instead, it has CURRENT_TIMESTAMP which-however-doesn't return a value of
TIMESTAMP WITH TIME ZONE, but rather a value of MSSQL's DATETIME type (which
doesn't contain time zone information).
Documentation
MySQL Follows the standard.
Documentation
Oracle Follows the standard.
Informix On my TODO.
Thanks,
shyam_sar...@yahoo.com
RE: Need LOCALTIMESTAMP ?
Hi Ashish,
Read about the latest TIMESTAMP implementation in MySQL 5.0 version and suggest
::
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
Also please comment on the following MySQL 5.0 implementation semantics::
TIMESTAMP values are converted from the current time zone to UTC for storage,
and converted back from UTC to the current time zone for retrieval. (This
occurs only for the TIMESTAMP data type, not for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time.
Should we do the same thing?
Thanks,
shyam_sar...@yahoo.com
--- On Wed, 2/11/09, Ashish Thusoo athu...@facebook.com wrote:
From: Ashish Thusoo athu...@facebook.com
Subject: RE: Need LOCALTIMESTAMP ?
To: hive-dev@hadoop.apache.org hive-dev@hadoop.apache.org,
shyam_sar...@yahoo.com shyam_sar...@yahoo.com
Date: Wednesday, February 11, 2009, 2:55 PM
Hi Shyam,
I think HIVE-192 is about the fact that there is no support
for the timestamp type in Hive (or for that matter date and
datetime types). In FB we are using strings to hold this
information.
If you are planning to add a built in function like
localtimestamp, then that should probably go into a
different JIRA.
We have tried to adhere to mysql way of doing things as we
find more folks using it (at least in our company) and looks
from your research that they are basically standards
compliant. So my vote will be to go with mysql semantics and
CURRENT_TIMESTAMP construct.
Ashish
-Original Message-
From: Shyam Sarkar [mailto:shyam_sar...@yahoo.com]
Sent: Wednesday, February 11, 2009 2:37 PM
To: hive-dev@hadoop.apache.org
Subject: Need LOCALTIMESTAMP ?
Hello,
Please help me to understand what I am going to implement
for Timestamp. Do we need LOCALTIMESTAMP implementation? See
the comparisons below::
=
LOCALTIMESTAMP
It's often important to get the value of current date
and time. Below are the functions used to do that in the
different implementations.
Standard The current timestamp (without time zone) is
retrieved with the LOCALTIMESTAMP function which may be used
as:
SELECT LOCALTIMESTAMP ...
or
SELECT LOCALTIMESTAMP(precision) ...
Note that SELECT LOCALTIMESTAMP() ... is
illegal: If you don't care about the precision, then you
must not use any parenthesis.
If the DBMS supports the non-core time zone features
(feature ID F411), then it must also provide the functions
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP(precision) which
return a value of type TIMESTAMP WITH TIME ZONE. If it
doesn't support time zones, then the DBMS must not
provide a CURRENT_TIMESTAMP function.
PostgreSQL Follows the standard.
Documentation
DB2 Doesn't have the LOCALTIMESTAMP function.
Instead, it provides a special, magic value ('special
register' in IBM language), CURRENT_TIMESTAMP (alias to
'CURRENT TIMESTAMP') which may be used as though it
were a function without arguments. However, since DB2
doesn't provide TIMESTAMP WITH TIME ZONE support, the
availability of CURRENT_TIMESTAMP could be said to be
against the standard-at least confusing.
Documentation
MSSQL Doesn't have the LOCALTIMESTAMP function.
Instead, it has CURRENT_TIMESTAMP which-however-doesn't
return a value of TIMESTAMP WITH TIME ZONE, but rather a
value of MSSQL's DATETIME type (which doesn't
contain time zone information).
Documentation
MySQL Follows the standard.
Documentation
Oracle Follows the standard.
Informix On my TODO.
Thanks,
shyam_sar...@yahoo.com
