| Author |
Message
|
| ase2015 |
 Posted: Sun May 13, 2018 7:23 am Post subject: CURRENT_TIMESTAMP is not accurate |
 |
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
Hi all
I'm facing problem with IIB v9.0.0.4 over aix
that's CURRENT_TIMESTAMP function return server date time +1 hour
ex
aix date result
Mon May 14 08:44:27 EET 2018
current_timestamp
14-MAY-18 09.43.20.153456000 AM
Does any one faced an issues similar to this
Last edited by ase2015 on Sun May 13, 2018 10:54 pm; edited 1 time in total |
|
| Back to top |
|
 |
| exerk |
| Posted: Sun May 13, 2018 7:58 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
1. EET is 'local' time, for wherever you are in Eastern Europe (?), in 24-hr clock format.
2. current_timestamp is also 'local' time, but in 12-hr clock format, with an offset (+02:00) to your 'baseline' time zone.
For example, UTC/GMT if used as the 'baseline' time zone for me should show:
| Code: |
Sun May 13 16:45:22 BST 2018
13-MAY-18 04.45.55.740481000 PM +01:00 |
So, my time zone, when I'm typing this:
| Code: |
17:55 Sunday, Eastern European Time (EET) is
15:55 Sunday, Greenwich Mean Time (GMT) |
The 'issue' is how you are interpreting it, not with the time being returned from the server.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| ase2015 |
| Posted: Sun May 13, 2018 11:15 pm Post subject: |
|
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
Sorry exerk
I missed writing the case and I updated it
the problem is not on the date/time format with it's 12 or 24
the reason is that the 1hour extra on the current_time that the aix system date |
|
| Back to top |
|
|
| exerk |
| Posted: Mon May 14, 2018 1:22 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
I suggest you read THIS.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| ase2015 |
| Posted: Mon May 14, 2018 3:57 am Post subject: |
|
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
| exerk wrote: |
| I suggest you read THIS. |
I'm afraid there is a misunderstanding
the aix date is correct to my time zone
the problem is on iib current_timestamp
in all cases i'll check the article |
|
| Back to top |
|
|
| exerk |
| Posted: Mon May 14, 2018 3:58 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
And is IIB interrogating the server to get that time stamp?
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 14, 2018 5:19 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ase2015 wrote: |
I'm afraid there is a misunderstanding
the aix date is correct to my time zone
the problem is on iib current_timestamp
in all cases i'll check the article |
Having resolved the article, check with your AIX system admins.
If they've configured the OS to show the time (including daylight savings) AND set the daylight savings flag then IIB will mistakenly add the hour again.
One quick check you could perform is to obtain the value of CURRENT_GMTTIMESTAMP from IIB. If you're in a period of daylight savings and IIB is returning the same value from both functions, the OS is misconfigured.
Experience teaches me that a distressingly large number of sys admins deal with daylight savings by changing the system clock +/- 1 hour on the weekends in question. This causes all sorts of problems with software like IIB & MQ which interrogate the daylight savings settings.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon May 14, 2018 10:30 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| ase2015 wrote: |
| exerk wrote: |
| I suggest you read THIS. |
I'm afraid there is a misunderstanding
the aix date is correct to my time zone
the problem is on iib current_timestamp
in all cases i'll check the article |
did you check this for the user running the broker (IIB)?
How did you determine that the aix date is correct?
What was the value of the timezone variable?
What is the difference between the time you see and GMT/UTC time as displayed by your system? Does it match what you expect?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ase2015 |
| Posted: Mon May 14, 2018 10:55 pm Post subject: |
|
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
| fjb_saper wrote: |
| ase2015 wrote: |
| exerk wrote: |
| I suggest you read THIS. |
I'm afraid there is a misunderstanding
the aix date is correct to my time zone
the problem is on iib current_timestamp
in all cases i'll check the article |
did you check this for the user running the broker (IIB)?
How did you determine that the aix date is correct?
What was the value of the timezone variable?
What is the difference between the time you see and GMT/UTC time as displayed by your system? Does it match what you expect?
Have fun |
yes my check was with iib user.
the aix command is correct as it's the correct time for our zone /region
to get aix time zone value by running echo $((`date +%H` - `date -u +%H`))
the result is 2 |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue May 15, 2018 4:50 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I stand by my previous advice - ask IIB for the values of CURRENT_TIMESTAMP and CURRENT_GMTTIMESTAMP are. If you're in a daylight savings area they should be reflect that, and if you're not on GMT they should reflect that as well.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ase2015 |
| Posted: Wed May 16, 2018 12:01 am Post subject: |
|
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
| Vitor wrote: |
| I stand by my previous advice - ask IIB for the values of CURRENT_TIMESTAMP and CURRENT_GMTTIMESTAMP are. If you're in a daylight savings area they should be reflect that, and if you're not on GMT they should reflect that as well. |
here you are the result
GMTTIMESTAMP '2018-05-16 07:47:51.029794'
TIMESTAMP '2018-05-16 10:47:51.029794'
AIXSysDate Wed May 16 09:49:11 EET 2018 |
|
| Back to top |
|
|
| exerk |
| Posted: Wed May 16, 2018 1:37 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
| ase2015 wrote: |
| Vitor wrote: |
| I stand by my previous advice - ask IIB for the values of CURRENT_TIMESTAMP and CURRENT_GMTTIMESTAMP are. If you're in a daylight savings area they should be reflect that, and if you're not on GMT they should reflect that as well. |
here you are the result
GMTTIMESTAMP '2018-05-16 07:47:51.029794'
TIMESTAMP '2018-05-16 10:47:51.029794'
AIXSysDate Wed May 16 09:49:11 EET 2018 |
EET is GMT +2, so the AIX System Date (give or take the two minutes) is correct for when you ran the interrogative. Your TIMESTAMP is +1 ahead of the AIX System Date, so as Vitor suggests, speak to your AIX System Administrators because 'something is rotten in the state of Denmark'.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| ase2015 |
| Posted: Wed May 16, 2018 3:07 am Post subject: |
|
|
Novice
Joined: 02 Sep 2015
Posts: 16
|
| exerk wrote: |
| ase2015 wrote: |
| Vitor wrote: |
| I stand by my previous advice - ask IIB for the values of CURRENT_TIMESTAMP and CURRENT_GMTTIMESTAMP are. If you're in a daylight savings area they should be reflect that, and if you're not on GMT they should reflect that as well. |
here you are the result
GMTTIMESTAMP '2018-05-16 07:47:51.029794'
TIMESTAMP '2018-05-16 10:47:51.029794'
AIXSysDate Wed May 16 09:49:11 EET 2018 |
EET is GMT +2, so the AIX System Date (give or take the two minutes) is correct for when you ran the interrogative. Your TIMESTAMP is +1 ahead of the AIX System Date, so as Vitor suggests, speak to your AIX System Administrators because 'something is rotten in the state of Denmark'. |
why I should check with unix admin if the system date is correct and the problem is that current_timestamp is +1 hour ahead |
|
| Back to top |
|
|
| exerk |
| Posted: Wed May 16, 2018 3:19 am Post subject: |
|
|
Jedi Council
Joined: 02 Nov 2006
Posts: 6339
|
| ase2015 wrote: |
| why I should check with unix admin if the system date is correct and the problem is that current_timestamp is +1 hour ahead |
Because as has been pointed out:
1. GMTTIMESTAMP correctly gives GMT (Zulu time);
2. AIXSysDate correctly gives EET time (local, or Bravo time, which is +2 Zulu);
3. TIMESTAMP gives EET +1 (Weird Al Yankovitch time).
One of the above appears to be wrong, and I'll give you 3 guesses as to which.
Again, ask your AIX System Admins how they set the clock on the server, and do they do anything squirrelly to adjust that time.
_________________
It's puzzling, I don't think I've ever seen anything quite like this before...and it's hard to soar like an eagle when you're surrounded by turkeys. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 16, 2018 5:35 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ase2015 wrote: |
| why I should check with unix admin if the system date is correct and the problem is that current_timestamp is +1 hour ahead |
Because, as I've said above, IIB is interpreting the system date (which seems to have been set to the correct time including daylight savings) as if it doesn't include daylight savings.
Within AIX, you can use the LOCALE to control if the system date is GMT, local time or local time honoring daylight savings. Your system seems to be set to indicate that the system date is local time, but the actual value includes the daylight savings offset.
Hence IIB is reacting as designed - taking what it's been told is the local time without daylight savings and adding the offset to it. Your sys admins need to either:
- retard the system date one hour to align it with the LOCALE setting
- change the LOCALE setting to indicate the system date already has the daylight saving offset added
Or you need to build code to manually handle daylight savings. IIB is doing what your AIX system is telling it that it needs to do. This isn't a bug in IIB.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
