| Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26584] |
Thu, 13 September 2001 09:44  |
Olaf Stetzer
Messages: 39
Registered: September 2001
|
Member |
|
|
Hello,
as mentioned in my last posting I want to convert
Date/Time from Microsoft(Excel)-Format (based on
1.1.1900= day 1) into Julian Seconds and then from JS
into the ODBC-SQL-TIMESTAMP-struct.
For the last conversion I've written a function
which basically runs js2ymds to get the values
for y m d and then computes hh mm ss from the
resulting seconds.
All values are then stored in the aforementioned struct.
I now tried to compute the js from MS-Format and then
use my function to store this in the sqlstruct:
---
function mstime2sqlts, msdate, mstime
jsdate=(msdate-1)*86400d0-3155673600d0
js=mstime*86400d0+jsdate
sqlts=js2sqlts(js)
return, sqlts
end
---
However I realised that the dates which result from
my computation differ from the ones that I get when
I just change the format setting in Excel!
Some Examples:
msdate msdate (formatted) js2ymds
1 1900 1 1 1900 1 1
366 1900 12 31 1901 1 1
12481 1934 3 3 1934 3 4
36682 2000 6 5 2000 6 6
It seems that in the MS-year 1900 has 1 more day
compared to the one calculated by js2ymds!! I don't
know if the fault is in Microsoft Excel or in the
function js2ymds but maybe in 1900 there was an
extra switching day (german: Schalttag) which is
not calculated in the function?
Greetings,
Olaf
--
Dr. Olaf Stetzer
Forschungszentrum Karlsruhe
Institut f�r Meterologie und Klimaforschung
Atmosph�rische Aerosole (IMK III) - http://imk-aida.fzk.de
Tel.: +49(0)7247-82-3249 (FAX: -4332)
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26712 is a reply to message #26584] |
Fri, 14 September 2001 03:14  |
R.Bauer
Messages: 1424
Registered: November 1998
|
Senior Member |
|
|
Olaf Stetzer wrote:
>
> Reimar Bauer schrieb:
>>
>> Olaf Stetzer wrote:
>>>
>>> Hello,
>>>
>>> as mentioned in my last posting I want to convert
>>> Date/Time from Microsoft(Excel)-Format (based on
>>> 1.1.1900= day 1) into Julian Seconds and then from JS
>>> into the ODBC-SQL-TIMESTAMP-struct.
>>>
>>> For the last conversion I've written a function
>>> which basically runs js2ymds to get the values
>>> for y m d and then computes hh mm ss from the
>>> resulting seconds.
>>>
>>> All values are then stored in the aforementioned struct.
>>>
>>> I now tried to compute the js from MS-Format and then
>>> use my function to store this in the sqlstruct:
>>> ---
>>> function mstime2sqlts, msdate, mstime
>>>
>>> jsdate=(msdate-1)*86400d0-3155673600d0
>>> js=mstime*86400d0+jsdate
>>> sqlts=js2sqlts(js)
>>> return, sqlts
>>>
>>> end
>>> ---
>>
>> Dear Olaf,
>>
>> We are calculating the jstime from Microsoft special time in this way
>>
>> js_time= mstime*24d*3600+xls2js(msdates)
>>
>
> Are you sure that this function is aware of the 29.2.1900-bug in
> ms-date? By looking at the code for xls2js I am not sure about this!
>
> Olaf
>
I have tested it with some of your data and I got the right days back.
And the routine is tested since 1998 many time by others too.
Reimar
--
Reimar Bauer
Institut fuer Stratosphaerische Chemie (ICG-1)
Forschungszentrum Juelich
email: R.Bauer@fz-juelich.de
http://www.fz-juelich.de/icg/icg1/
============================================================ ======
a IDL library at ForschungsZentrum Juelich
http://www.fz-juelich.de/icg/icg1/idl_icglib/idl_lib_intro.h tml
http://www.fz-juelich.de/zb/text/publikation/juel3786.html
============================================================ ======
read something about linux / windows
http://www.suse.de/de/news/hotnews/MS.html
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26713 is a reply to message #26584] |
Fri, 14 September 2001 02:39 |
Olaf Stetzer
Messages: 39
Registered: September 2001
|
Member |
|
|
> function mstime2sqlts, msdatetime
> return, js2sqlts((msdatetime-36526d0)*86400d0)
> end
>
Changed to:
function mstime2sqlts, msdatetime
if floor(msdatetime) eq 60 then begin
print, 'WARNING!'
print, ' You tried to convert a date with value 60'
print, ' which corresponds to 29.2.1900 according'
print, ' to Micro$oft! However this date does not'
print, ' exist and is converted to 1.3.1900!'
end
if msdatetime lt 61 then msdatetime=msdatetime+1
return, js2sqlts((msdatetime-36526d0)*86400d0)
end
Now i feel much better!!! :-)
Olaf
--
Dr. Olaf Stetzer
Forschungszentrum Karlsruhe
Institut f�r Meterologie und Klimaforschung
Atmosph�rische Aerosole (IMK III) - http://imk-aida.fzk.de
Tel.: +49(0)7247-82-3249 (FAX: -4332)
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26714 is a reply to message #26584] |
Fri, 14 September 2001 02:18 |
Olaf Stetzer
Messages: 39
Registered: September 2001
|
Member |
|
|
Reimar Bauer schrieb:
>
> Olaf Stetzer wrote:
>>
>> Hello,
>>
>> as mentioned in my last posting I want to convert
>> Date/Time from Microsoft(Excel)-Format (based on
>> 1.1.1900= day 1) into Julian Seconds and then from JS
>> into the ODBC-SQL-TIMESTAMP-struct.
>>
>> For the last conversion I've written a function
>> which basically runs js2ymds to get the values
>> for y m d and then computes hh mm ss from the
>> resulting seconds.
>>
>> All values are then stored in the aforementioned struct.
>>
>> I now tried to compute the js from MS-Format and then
>> use my function to store this in the sqlstruct:
>> ---
>> function mstime2sqlts, msdate, mstime
>>
>> jsdate=(msdate-1)*86400d0-3155673600d0
>> js=mstime*86400d0+jsdate
>> sqlts=js2sqlts(js)
>> return, sqlts
>>
>> end
>> ---
>
> Dear Olaf,
>
> We are calculating the jstime from Microsoft special time in this way
>
> js_time= mstime*24d*3600+xls2js(msdates)
>
Are you sure that this function is aware of the 29.2.1900-bug in
ms-date? By looking at the code for xls2js I am not sure about this!
Olaf
--
Dr. Olaf Stetzer
Forschungszentrum Karlsruhe
Institut f�r Meterologie und Klimaforschung
Atmosph�rische Aerosole (IMK III) - http://imk-aida.fzk.de
Tel.: +49(0)7247-82-3249 (FAX: -4332)
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26715 is a reply to message #26584] |
Fri, 14 September 2001 02:10 |
Olaf Stetzer
Messages: 39
Registered: September 2001
|
Member |
|
|
Reimar Bauer schrieb:
>
>
> Dear Olaf,
>
> We are calculating the jstime from Microsoft special time in this way
>
> js_time= mstime*24d*3600+xls2js(msdates)
>
Hello Reimar,
I just changed my code to:
function mstime2sqlts, msdatetime
return, js2sqlts((msdatetime-36526d0)*86400d0)
end
assuming that msdatetime is the result of mstime+msdate.
36526 is the MS-date for 1.1.2000! I know that
this shifts the problem only to dates prior to 1.3.1900 but for the
moment thats OK. I will have a look at your suggestion soon!
Olaf
--
Dr. Olaf Stetzer
Forschungszentrum Karlsruhe
Institut f�r Meterologie und Klimaforschung
Atmosph�rische Aerosole (IMK III) - http://imk-aida.fzk.de
Tel.: +49(0)7247-82-3249 (FAX: -4332)
|
|
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26717 is a reply to message #26584] |
Fri, 14 September 2001 00:34 |
R.Bauer
Messages: 1424
Registered: November 1998
|
Senior Member |
|
|
Harald Giese wrote:
>
> Olaf Stetzer wrote:
>> ...
>> It seems that in the MS-year 1900 has 1 more day
>> compared to the one calculated by js2ymds!! I don't
>> know if the fault is in Microsoft Excel or in the
>> function js2ymds but maybe in 1900 there was an
>> extra switching day (german: Schalttag) which is
>> not calculated in the function?
>
> Hi Olaf,
>
> MS-Excel has indeed two peculiarities (at least):
>
> 1. it counts the 01.01.1900 00:00 as Julian day 1.0 (where most of us
> would expect 0.0)
> 2. it has a "29.02.1900" - though 1900 was definitely not a leap year
>
> Happy coding!
>
> Regards,
> Harald
I have seen this problems by storing data with labview too. It seems to
me
that it comes from the Microft Windows.
I haven't tried to program a fix around this, because our exchange
format
from Labview to IDL is an ASCII file format named ENZ.
In this file format we are able to set a start date then only seconds of
day
have to be stored. Later on the read_enz() routine translates the date
seconds
information correctly in julian seconds. Result is the ICG data
structure.
Some more words about ENZ.
Normally data is a bit more as only values.
You like to know the units, the long name (for printouts), the missing
value,
the short name of the data probably a scaling factor or an offset
as well as who did the experiment and why and where
and which resources are used and some more.
Our data is always described in the data files.
This information is defined as GLOBAL comments and PARAMETER comments.
HDF, netCDF handles this informtion the same way.
ENZ is specially for vector data and we are using it for Experiments if
we like or need ASCII data for exchange.
regards
Reimar
--
Reimar Bauer
Institut fuer Stratosphaerische Chemie (ICG-1)
Forschungszentrum Juelich
email: R.Bauer@fz-juelich.de
http://www.fz-juelich.de/icg/icg1/
============================================================ ======
a IDL library at ForschungsZentrum Juelich
http://www.fz-juelich.de/icg/icg1/idl_icglib/idl_lib_intro.h tml
http://www.fz-juelich.de/zb/text/publikation/juel3786.html
============================================================ ======
read something about linux / windows
http://www.suse.de/de/news/hotnews/MS.html
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26718 is a reply to message #26584] |
Fri, 14 September 2001 00:06 |
Olaf Stetzer
Messages: 39
Registered: September 2001
|
Member |
|
|
Harald Giese schrieb:
>
> Olaf Stetzer wrote:
>> ...
>> It seems that in the MS-year 1900 has 1 more day
>> compared to the one calculated by js2ymds!! I don't
>> know if the fault is in Microsoft Excel or in the
>> function js2ymds but maybe in 1900 there was an
>> extra switching day (german: Schalttag) which is
>> not calculated in the function?
>
> Hi Olaf,
>
> MS-Excel has indeed two peculiarities (at least):
>
> 1. it counts the 01.01.1900 00:00 as Julian day 1.0 (where most of us
> would expect 0.0)
> 2. it has a "29.02.1900" - though 1900 was definitely not a leap year
*ROTFLOL* !!!!!! I first thought about this possibility but did not
belive that MS can make such a silly mistake!!!! But in the end it just
supports my image of this $$ company.... Do they know about this and
what is their official reaction? Let me guess.... They try to redefine
the calendar so their calcualtions are correct (you know the joke
about MS-engineers trying to repair a broken light bulb?)
.
.
.
MS redefined darkness as new standard!!!
Greetings,
Olaf
--
Dr. Olaf Stetzer
Forschungszentrum Karlsruhe
Institut f�r Meterologie und Klimaforschung
Atmosph�rische Aerosole (IMK III) - http://imk-aida.fzk.de
Tel.: +49(0)7247-82-3249 (FAX: -4332)
|
|
|
|
| Re: Error in function js2ymds (JHU/APL/S1R IDL Lib)? [message #26720 is a reply to message #26584] |
Thu, 13 September 2001 17:09 |
Harald Giese
Messages: 1
Registered: September 2001
|
Junior Member |
|
|
Olaf Stetzer wrote:
> ...
> It seems that in the MS-year 1900 has 1 more day
> compared to the one calculated by js2ymds!! I don't
> know if the fault is in Microsoft Excel or in the
> function js2ymds but maybe in 1900 there was an
> extra switching day (german: Schalttag) which is
> not calculated in the function?
Hi Olaf,
MS-Excel has indeed two peculiarities (at least):
1. it counts the 01.01.1900 00:00 as Julian day 1.0 (where most of us
would expect 0.0)
2. it has a "29.02.1900" - though 1900 was definitely not a leap year
Happy coding!
Regards,
Harald
|
|
|
|
comp.lang.idl-pvwave archive
Members
Search
Help
Login
Home
