Hi,
Post by Claudio Valderrama C.-----Original Message-----
Caine
Starting to hit a major problem with inter database compatibility,
relating to NOW and CURRENT_TIMESTAMP. The convention seems to be that
CURRENT_TIMESTAMP returns a UTC normalized time while LOCAL_TIMESTAMP
and NOW provides the server time.
Where is this convention, in other databases or in the SQL spec or in FB?
I ask because we don't have LOCAL_TIMESTAMP.
It's a SQL spec. According to the SQL-2003 standard, we shouldn't even
have CURRENT_TIMESTAMP, because we don't save the time zone. And IIRC,
this discussion did take place a few months ago, either here in
Firebird-Devel or Firebird-Architect. And, i think at the time the
discussion was about the precision, not time zones. But, if we want
to follow the SQL standard(especially if other RDBMS are following the
same convention), we should seriously look at making those changes.
Maybe(?) it's too late for FB 2, but my vote would be at least for FB
3, with a warning in FB 2 release notes that CURRENT_TIMESTAMP and
CURRENT_TIME would have a different behavior in FB 3. And, maybe add
LOCALTIMESTAMP and LOCALTIME to FB 2, so people can start making the
appropriate changes for FB 3. And, it will require a change on how
timestamps are saved physically, because with time zone data it should
be able to save 25 positions.
Here's an example of a possible value to be stored(at least how I see
it): "2005-12-02 12:30:35.111111 -04:00"
The data might be actually stored as: "20051202123035111111-0400"
I know a change in behavior is not very much welcomed, but this change
would put Firebird more in line with the SQL standard and other RDBMS.
And, here the time zone should come from the client, not just the
server. Because both might be in different time zones.
Here's the SQL-2003 definition:
[Start Quote...]
6.31 <datetime value function>
Function:
Specify a function yielding a value of type datetime.
Format:
<datetime value function> ::=
<current date value function>
| <current time value function>
| <current timestamp value function>
| <current local time value function>
| <current local timestamp value function>
<current date value function> ::= CURRENT_DATE
<current time value function> ::=
CURRENT_TIME [ <left paren> <time precision> <right paren> ]
<current local time value function> ::=
LOCALTIME [ <left paren> <time precision> <right paren> ]
<current timestamp value function> ::=
CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
<current local timestamp value function> ::=
LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
Syntax Rules:
1) The declared type of a <current date value function> is DATE. The
declared type of a <current time value function> is TIME WITH TIME
ZONE. The declared type of a <current timestamp value function> is
TIMESTAMP WITH TIME ZONE. NOTE 117 See the Syntax Rules of Subclause
6.1, <data type>, for rules governing <time precision> and
<timestamp precision>.
2) If <time precision> TP is specified, then LOCALTIME(TP) is
equivalent to:
CAST (CURRENT_TIME(TP) AS TIME(TP) WITHOUT TIME ZONE)
Otherwise, LOCALTIME is equivalent to:
CAST (CURRENT_TIME AS TIME WITHOUT TIME ZONE)
3) If <timestamp precision> TP is specified, then LOCALTIMESTAMP(TP)
is equivalent to:
CAST (CURRENT_TIMESTAMP(TP) AS TIMESTAMP(TP) WITHOUT TIME ZONE)
Otherwise, LOCALTIMESTAMP is equivalent to:
CAST (CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE)
Access Rules:
None.
General Rules:
1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP respectively return the current date, current time,
and current timestamp; the time and timestamp values are returned with
time zone displacement equal to the current default time zone
displacement of the SQLsession.
2) If specified, <time precision> and <timestamp precision>
respectively determine the precision of the time or timestamp value
returned.
3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value function>s that
are contained in <value expression>s that are generally contained,
without an intervening <routine invocation> whose subject routines do
not include an SQL function, either in S without an intervening <SQL
procedure statement> or in an <SQL procedure statement> contained in
the <triggered action> of a trigger activated as a consequence of
executing S, are effectively evaluated simultaneously. The time of
evaluation of a <datetime value function> during the execution of S
and its activated triggers is implementation-dependent.
NOTE 118 Activation of triggers is defined in Subclause 4.38.2, Trigger execution.
Conformance Rules
1) Without Feature F555, Enhanced seconds precision, conforming SQL
language shall not contain a <current local time value function> that
contains a <time precision> that is not 0 (zero).
2) Without Feature F555, Enhanced seconds precision, conforming SQL
language shall not contain a <current local timestamp value function>
that contains a <timestamp precision> that is neither 0 (zero) nor 6.
3) Without Feature F411, Time zone specification, conforming SQL
language shall not contain a <current time value function>.
4) Without Feature F411, Time zone specification, conforming SQL
language shall not contain a <current timestamp value function>.
[...End Quote]
Here's the SQL-2003 definition for the data type:
[Start Quote...]
<datetime type> ::=
DATE
| TIME [ <left paren> <time precision> <right paren> ] [ <with or without time zone> ]
| TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
[ <with or without time zone> ]
<with or without time zone> ::=
WITH TIME ZONE
| WITHOUT TIME ZONE
<time precision> ::= <time fractional seconds precision>
<timestamp precision> ::= <time fractional seconds precision>
<time fractional seconds precision> ::= <unsigned integer>
Syntax Rules:
[...]
28) If <time precision> is not specified, then 0 (zero) is implicit.
If <timestamp precision> is not specified, then 6 is implicit.
29) If <with or without time zone> is not specified, then WITHOUT TIME
ZONE is implicit.
30) The maximum value of <time precision> and the maximum value of
<timestamp precision> shall be the same implementation-defined value
that is not less than 6. The values of <time precision> and <timestamp
precision> shall not be greater than that maximum value.
31) The length of a DATE is 10 positions. The length of a TIME WITHOUT
TIME ZONE is 8 positions plus the <time fractional seconds precision>,
plus 1 (one) position if the <time fractional seconds precision> is
greater than 0 (zero). The length of a TIME WITH TIME ZONE is 14
positions plus the <time fractional seconds precision> plus 1 (one)
position if the <time fractional seconds precision> is greater than 0
(zero). The length of a TIMESTAMP WITHOUT TIME ZONE is 19 positions
plus the <time fractional seconds precision>, plus 1 (one) position if
the <time fractional seconds precision> is greater than 0 (zero). The
length of a TIMESTAMP WITH TIME ZONE is 25 positions plus the <time
fractional seconds precision> plus 1 (one) position if the <time
fractional seconds precision> is greater than 0 (zero).
General Rules:
[...]
4) For a <datetime type>,
Case:
a) If DATE is specified, then the data type contains the <primary
datetime field>s years, months, and days.
b) If TIME is specified, then the data type contains the <primary
datetime field>s hours, minutes, and seconds.
c) If TIMESTAMP is specified, then the data type contains the <primary
datetime field>s years, months, days, hours, minutes, and seconds.
d) If WITH TIME ZONE is specified, then the data type contains the
time zone datetime fields.
NOTE 83 Within the non-null values of a <datetime type>, the value
of the time zone interval is in the range 13:59 to +14:00. The range
for time zone intervals is larger than many readers might expect
because it is governed by political decisions in governmental bodies
rather than by any natural law.
NOTE 84 A <datetime type> contains no other fields than those
specified by the preceding Rule.
5) For a <datetime type>, a <time fractional seconds precision> that
is an explicit or implicit <time precision> or <timestamp precision>
defines the number of decimal digits following the decimal point in
the SECOND <primary datetime field>.
[...End Quote]
--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)