| Author |
Message
|
| kishoreraju |
 Posted: Mon Mar 15, 2010 8:13 am Post subject: Converting Date to Milliseconds |
 |
|
Disciple
Joined: 30 Sep 2004
Posts: 156
|
Hi All,
I have created a function to convert to Date into milliseconds and it is woking fine till 14 March. From 15 onwards it is giving incorrect values
The below is the function i have created
| Code: |
CREATE FUNCTION ConvertDateInMilliSeconds(IN PrintDate CHARACTER ) RETURNS INTEGER
BEGIN
DECLARE CurrentZoneInMilliseconds INTEGER (ABS(CAST(LOCAL_TIMEZONE AS INTEGER)* 60) * 1000);
DECLARE CreatedDate GMTTIMESTAMP cast((PrintDate||' 00:00:00') as GMTTIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');
DECLARE EpocDate GMTTIMESTAMP CAST('1970-01-01 00:00:00' as GMTTIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ss');
DECLARE DiffInMilliSeconds INTEGER;
DECLARE DateInMilliSeconds INTEGER;
SET DiffInMilliSeconds=CAST(((CreatedDate-EpocDate) Day ) AS INTEGER);
SET DateInMilliSeconds=(DiffInMilliSeconds * 24 * 60 * 60*1000)+CurrentZoneInMilliseconds;
RETURN DateInMilliSeconds;
END; |
When i pass 2010-02-01 as input ,The millisecond value given from this function is 1264996800000.
When i convert the above milliseconds into Date by using the below link
it is getting converted as Sun Jan 31 23:00:00 EST 2010 which is one hour less than actual value.
http://www.esqsoft.com/javascript_examples/date-to-epoch.htm
My understanding is daylight svaing is not applied properly. Can anytell how i can fix this.
Thanks in advance |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Mar 15, 2010 8:42 am Post subject: Re: Converting Date to Milliseconds |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kishoreraju wrote: |
My understanding is daylight svaing is not applied properly. Can anytell how i can fix this.
|
Yes - stop using GMT in your function!
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kishoreraju |
| Posted: Mon Mar 15, 2010 8:45 am Post subject: |
|
|
Disciple
Joined: 30 Sep 2004
Posts: 156
|
Thanks for quick response.
I tried with TIMESTAMP, and DATE as well but no luck. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Mar 15, 2010 8:51 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kishoreraju wrote: |
| I tried with TIMESTAMP, and DATE as well but no luck. |
In that case, stop using GMT on your box.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Mar 15, 2010 8:54 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Or, alternatively, use GMT everywhere 
FWIW I've spent my morning picking bits of logs out of client machines. Half of them were put on daylight savings at the weekend, half were not but should have been and half that should have been left on GMT were changed.
(Yes. 3 halves. That's been bothering me too.)
The Trout Of Timekeeping has been wielded at a particular group of admins who apply DST by changing the base time... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Mar 15, 2010 12:00 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
 with Vitor.
The time on a box should always be set in GMT.
The login parameters for the particular user then need to have the TZ information that will transform this date/time from GMT to local...
Alternatively some computers' time is set to the time zone they are physically in. However here too the principle applies. The time needs to be set correctly and not changed manually when DST is applied...
I still prefer that computer time be GMT and the users have a different TZ.... in their profile.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Mar 15, 2010 12:43 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| fjb_saper wrote: |
The time on a box should always be set in GMT.
|
This is something that an awful lot of sysadmins just don't get especially in places where GMT is actually correct for 5 months a year.
Having the system time = GMT has benefits when you are using WMQ which uses GMT by default.
_________________
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 |
|
|
| AndreasMartens |
| Posted: Wed Mar 17, 2010 8:02 am Post subject: LOCAL_TIMEZONE not so much |
|
|
Acolyte
Joined: 30 Jan 2006
Posts: 65
Location: Hursley, UK
|
Hi,
You have unfortunately fallen foul of the problem that LOCAL_TIMEZONE is the *current* timezone offset of the broker (well, it's the timezone offset taken at the point in time in which you entered the current compute node, as per the SQL specs, but enough of that).
Hence when you go past the 14th March and your system enters DST, the LOCAL_TIMEZONE will have one extra hour, regardless of the date/time you're currently processing 
cheers,
Andreas |
|
| Back to top |
|
|
|
|
