On 16-Oct-2015 18:35 -0500, Justin Dearing wrote:
On 16-Oct-2015 18:37 -0500, Justin Dearing wrote:
On 16-Oct-2015 16:08 -0500, Justin Dearing wrote:
It's not ISO 8601. does it adhere to a published standard, or is
it an IBM standard?
Yes, it's a bit of pedantry. However, I want to patch the
DateTime object in PHP, and possible python, and it would be an
easier sell to get my patch accepted into the core dateTime
object for an OS if I was supporting some ANSI/ISO standard as
opposed to "the format the IBM i uses".
According to the older docs
[
http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_i5_54/db2/rbafzmstch2data.htm#dtstrng]
"String representations of datetime values", the the DATE and TIMESTAMP
[and TIME] formats used by the SQL are "ANSI/ISO SQL standard":
Timestamp strings
Format: TIMESTAMP 'yyyy-mm-dd hh:mm:ss.nnnnnn'
Example: TIMESTAMP '1990-03-02 08:30:00.010000'
Date strings
Format: DATE 'yyyy-mm-dd'
Example: DATE '1987-10-12'
Time strings
Format: TIME 'hh:mm:ss'
Example: TIME '13:30:05'
By a newer release, the allowed fractional seconds [in a TimeStamp
string constant\literal] are expanded; there may be more or instead may
be fewer digits of precision [than the six used\required from the past].
<<SNIP>> ISO 8601. 2015-10-16T15:04:30-04:00 is an ISO date time,
DB2 wants 1981-04-28 00:00:00.0
The IBM DB2 for i SQL _accepts_ that format but /wants/ any of the
valid formats as appropriate for the context of a TIMESTAMP
specification. That includes a no-delimiters 14-character form
'yyyymmddhhmmss' and the IBM SQL format 'yyyy-mm-dd-hh.mm.ss.nnnnnn'.
The IBM SQL format might be the most prevalent, being the default form
for presentation of a TIMESTAMP value.
<<SNIP>> Is there a SQL scalar function that will take a string in
ISO 8601 format as input and output a TIMESTAMP column? <<SNIP>>
The TIMESTAMP_FORMAT [synonym TO_DATE] scalar function is what
*should* be functional to achieve that.
However that function is [still poorly documented IMO, despite my
request and their response, to update for clarity and to add more
examples] too limited in capabilities to achieve a direct conversion
from that, the best I know of, with the most recent support; from what I
can only read from the docs, not actually test, what are the
capabilities -- that are not well-expressed in any examples, which are
all basically the same example repeated... they had no, or just did not
apply any, imagination in what was provided :-( despite my suggestions.
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscatsformat.htm]
IBM i 7.2 ->Database ->Reference ->SQL reference ->Built-in functions
->Scalar functions ->VARCHAR_FORMAT
"VARCHAR_FORMAT
The VARCHAR_FORMAT function returns a character representation of a
timestamp in the format indicated by format-string.
>>-VARCHAR_FORMAT--(--expression--,--format-string--)------><
..."
For example, the following may be functional to convert the shown ISO
8601 timestamp string value into a TIMESTAMP [but I can not test; the
docs are not absolutely clear on the ability to mask data, for example,
because I am unsure if my inference is accurate, that because the
"Separator Characters specified in a string-expression are used to
separate components and are not required to match the separator
characters specified in the format-string", then any Sep-Char can
replace any character _positionally_, such as to replace the "T" in the
example data?:
TODATE('2015-10-16T15:04:30-04:00', 'YYYY-MM-DD:HH24:MM:SS-//://')
If so, then the following expression might get the UTC TimeStamp
value for the column DDL (ISO8601_TS_C25_COL for ITCC CHAR(25)):
TODATE(ITCC, 'YYYY-MM-DD:HH24:MM:SS-//://') +
substr(ITCC, 19, 3) hours +
( case SUBSTR(ITCC, 19, 1) when '-' then -1 else 1 end
* right(ITCC, 2) ) minutes
or if one imagines that the DB has some optimization for
identical\repeated sub-expressions, then maybe instead:
TODATE(ITCC, 'YYYY-MM-DD:HH24:MM:SS-//://') +
substr(ITCC, 19, 3) hours +
( sign( SUBSTR(ITCC, 19, 3) )
* right(ITCC, 2) ) minutes
FWiW, if I found a need to accept the ISO 8601 timestamp format
strings, and wanted to convert those into a stored TIMESTAMP value, then
likely I would just create my own User Defined Function (UDF) [scalar]
to effect the conversion. While not pretty if done so, the function
could be coded such that there is only the RETURN and the RETURN
expression of that function would then be capable of being processed [I
presume, I have no experience] by the database using the inline feature,
though presumably only if also making the function deterministic [which
is probably not a 100% accurate designation if the value is adjusted
according to current timezone, and that is required, if my cursory
thoughts on the conversion logic is correct, though scoped in a job with
a Time Zone unlikely to change.?.?]. Perhaps the following is correct?:
create function iso8601_ts ( ts varchar(25) )
returns timestamp
language sql deterministic
return
( timestamp( left(ts, 10) , substr(ts, 12, 8) )
+ dec(substr(ts, 20, 3), 2) hours
+ ( sign( dec(substr(ts, 20, 3), 2) )
* dec(right(ts, 2), 2) ) minutes )
- current timezone
midrange.com
