| Author |
Message
|
| EricCox |
 Posted: Thu Jun 28, 2012 11:51 am Post subject: CAST(cDate AS FORMAT TIMESTAMP 'yyyyMMddHHmmss') adds Hour |
 |
|
Master
Joined: 08 Apr 2011
Posts: 292
|
To all,
We are finding that this statement is incorrectly adding an hour to the time when it assigns the output to the variable.
Here is the ESQL Statement:
SET cYear = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranDt"."cfg-hdr:Year";
SET cMonth = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranDt"."cfg-hdr:Month";
SET cDay = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranDt"."cfg-hdr:Day";
SET cHour = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranTm"."cfg-hdr:Hr";
SET cMinutes = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranTm"."cfg-hdr:Min";
SET cSeconds = ref_MessageHeader."cfg-hdr:ControlOptions"."cfg-hdr:TellerControl"."cfg-hdr:TranTm"."cfg-hdr:Sec";
SET cDate = cYear || cMonth || cDay || cHour || cMinutes || cSeconds;
SET TRANSACTION_TS = CAST(cDate AS TIMESTAMP FORMAT 'yyyyMMddHHmmss');
And here is where the trace log shows the +1 hour behavior.
This resolved to ''CAST('20120206121731' AS TIMESTAMP FORMAT 'yyyyMMddHHmmss' )''. The result was ''TIMESTAMP '2012-02-06 13:17:31'
What should I do to stop it from doing +/- 1 hour offset given DST?
We are in EST. When we pass in the value as shown above in the CAST() we want that exact same time to be reflected in the output given to the SET statement.
Thanks,
Eric |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu Jun 28, 2012 12:42 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
All time values should be GMT. You should not be using any other timezone, no matter where you live. Computers never sleep. They don't need timezones.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jun 28, 2012 1:27 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
It does not add one hour! It may add one hour in certain cases...
If the Timestamp is not in DST but the broker is, translating to GMT timestamp may apply the current offset.
If you want to apply the offset at the date / time of the timestamp use the simple timeformat in Java to do your time transformations.
Otherwise as my esteemed colleague specified, all timestamp, date, or time values should always be expressed in an clear timezone agnostic manner: like '17:24:10.000000-04:00'
This clearly states that the time is local and the offset to GMT is -04:00 which would make it '21:24:10.000000+00:00' GMT and no need to know whether it uses local day light or not as the offset would change when the DST takes effect...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Jun 28, 2012 9:52 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| lancelotlinc wrote: |
| All time values should be GMT. You should not be using any other timezone, no matter where you live. Computers never sleep. They don't need timezones. |
Really?
1) You should really refere to UTC rather than GMT these days
2) What about the external systems that your computer is connected to and are sending messages to? Shouldn't you be using whatever time (+ TZ) they require?
I'm working on one system where we have to use 3 different times. UTC, Local and UTC-8. This is simply to allow Broker to intgrate correctly with the rest of the network. Broker is an integration tool as I'm sure you know and is ideal for this type of thing. Oh, the underlying DB uses LocalTime) and everything in the whole application suite is time driven. Events happen that affect the timings/scheduling of other events.
It might be true at your site that you keep everything in UTC time but that does not and cannot happen everywhere. Well done for keeping the TZ constant throughout your system others are not so fortunate
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Jun 29, 2012 2:43 am Post subject: Re: CAST(cDate AS FORMAT TIMESTAMP 'yyyyMMddHHmmss') adds Ho |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| EricCox wrote: |
| What should I do to stop it from doing +/- 1 hour offset given DST? |
Set cDate to conform to the TIMESTAMP literal format; don't use the FORMAT clause:
| Code: |
SET cDate = cYear || '-' || cMonth || '-' || cDay || ' ' || cHour || ':' || cMinutes || ':' || cSeconds;
SET TRANSACTION_TS = CAST(cDate AS TIMESTAMP); |
The rules for CASTing to/from TIMESTAMP are different, with or without the FORMAT clause.
Without a FORMAT clause, the CAST converts from CHARACTER to TIMESTAMP, and assumes no time zone.
With a FORMAT clause, the CAST converts from xsd:dateTime to TIMESTAMP, and assumes the current LOCAL_TIMEZONE offset.
If the xsd:dateTime does not specify a timezone offset, it assumes the local time zone.
| lancelotlinc wrote: |
| All time values should be GMT. You should not be using any other timezone, no matter where you live. |
No. Time values can refer to past events, future events, recurring events (where the timezone offset may vary), or floating time events (such as dates without times, or times without dates). They may require either a time zone, timezone offset, both, or neither.
Furthermore, adding a (GMT) timezone offset doesn't solve the given problem:
| Code: |
| SET TRANSACTION_TS = CAST('20120206171731+0' AS TIMESTAMP FORMAT 'yyyyMMddHHmmssZ'); |
This would still generate the wrong literal value for the TIMESTAMP. |
|
| Back to top |
|
|
| EricCox |
| Posted: Fri Jun 29, 2012 4:10 am Post subject: Hit the nail on the head |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
Thank You rekarm01 aka Shaman (I stand corrected)
You've spoken with great clarity.
I believe your description of how FORMAT works provides clarification on the behavior I'm seeing.
I don't want it to have any notion of timezone and simply CAST as a valid TIMESTAMP format so I can stuff it in the database.
Time here is generated by a front end source system and is always in local time with or without DST applied. Therefore, I need to preserve that date/time and not do any conversions to UTC/GMT.
I understand the other posters points about UTC/GMT however without the front end source system generating UTC/GMT I would be left with converting everything to UTC/GMT and showing the offset. Then in the reports and analytics layer I'd have to have them apply the offset to get the actual local time. Those are unnecessary steps if I just preserve the time without having the unintended behavior of applying any kind of offset for UTC/GMT/DST.
I'll try your suggested CAST without the FORMAT and will report back.
Thanks very much!
Last edited by EricCox on Fri Jun 29, 2012 4:18 am; edited 1 time in total |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 29, 2012 4:16 am Post subject: Re: Hit the nail on the head |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| EricCox wrote: |
| Thank You Shaman |
Are you "EricCox"? Or "Apprentice"? |
|
| Back to top |
|
|
| kash3338 |
| Posted: Fri Jun 29, 2012 6:33 am Post subject: Re: CAST(cDate AS FORMAT TIMESTAMP 'yyyyMMddHHmmss') adds Ho |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| rekarm01 wrote: |
| EricCox wrote: |
| What should I do to stop it from doing +/- 1 hour offset given DST? |
Set cDate to conform to the TIMESTAMP literal format; don't use the FORMAT clause:
| Code: |
SET cDate = cYear || '-' || cMonth || '-' || cDay || ' ' || cHour || ':' || cMinutes || ':' || cSeconds;
SET TRANSACTION_TS = CAST(cDate AS TIMESTAMP); |
The rules for CASTing to/from TIMESTAMP are different, with or without the FORMAT clause.
Without a FORMAT clause, the CAST converts from CHARACTER to TIMESTAMP, and assumes no time zone.
With a FORMAT clause, the CAST converts from xsd:dateTime to TIMESTAMP, and assumes the current LOCAL_TIMEZONE offset.
If the xsd:dateTime does not specify a timezone offset, it assumes the local time zone.
|
Thanks rekarm01 for excellent clarification on this query.
But for the OP to get the required format ('yyyyMMddHHmmss') I guess he needs to do a bit more. He can add the time zone identifier (append) to his input date and then use the CAST with FORMAT. Guess that will work for him to get the required output date format. |
|
| Back to top |
|
|
| EricCox |
| Posted: Fri Jun 29, 2012 6:39 am Post subject: Solution |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
rekarm01 is exactly correct.
The suggested change does not allow the date time value to be passed into the TIMESTAMP with no application of offset due to GMT/DST.
Thanks again! |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sun Jul 01, 2012 8:58 pm Post subject: Re: CAST(cDate AS FORMAT TIMESTAMP 'yyyyMMddHHmmss') adds Ho |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| kash3338 wrote: |
| But for the OP to get the required format ('yyyyMMddHHmmss') I guess he needs to do a bit more. He can add the time zone identifier (append) to his input date and then use the CAST with FORMAT. Guess that will work for him to get the required output date format. |
Except in the OP's case, the format applies to the input date, not the output. Strings are formattable, timestamps are not. Adding the correct time zone, or timezone offset, to the input string does not help, because the TIMESTAMP datatype does not support time zones; (neither do the DATE and TIME datatypes). |
|
| Back to top |
|
|
|
|
