"Result of date type attributes in Write Excel operator"

qwertz

Dear all,

I was wondering about the result of the "write excel" operator. When feeding dates (without time) into it the excel file contains the same date (not surprising so far) but with an additional time which seems to vary randomly all over the day.

??? ??? ???

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.2.003">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.2.003" expanded="true" name="Process">
    <process expanded="true" height="235" width="279">
      <operator activated="true" class="generate_sales_data" compatibility="5.2.003" expanded="true" height="60" name="Generate Sales Data" width="90" x="45" y="30"/>
      <operator activated="true" class="write_excel" compatibility="5.2.003" expanded="true" height="76" name="Write Excel" width="90" x="179" y="30">
        <parameter key="excel_file" value="c:\test.xls"/>
      </operator>
      <connect from_op="Generate Sales Data" from_port="output" to_op="Write Excel" to_port="input"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
    </process>
  </operator>
</process>

Sincerely
Sachs

Nils_Woehler

Hi,

the reason here is that the Generate Sales Data produces random timestamps that are only displayed as date.
If you write them to excel you see the exact date.

Best,
Nils

qwertz

I tried another setup:

- Manually create an excel sheet with dates (without time resp. hours)
- Read this sheet with "read excel" operator
- Write back this sheet with "write excel" operator

Result was the same. Excel shows date + time though time has never been specified.

Example from output file:
2006-03-05 01:00:00
2006-04-08 02:00:00
2006-04-10 02:00:00
2006-05-07 02:00:00


Cheers
Sachs

Nils_Woehler

Internally all dates are stored as a Java's Date class. This format is stored as milliseconds since 1970 thus it implicitly adds time to dates from excel  even if they are stored as date only.
Furthermore the default date format for the Write Excel operator is 'yyyy-MM-dd HH:mm:ss'.
Due to this two facts you see a time in the resulting excel file even if you haven't specified one before.

Best,
Nils

qwertz

Hi Nils,

thanks for letting me know. I now have a better understanding, why time appears in excel.

However, I'm still wondering that hours vary (e.g. "01:00:00" and "02:00:00" - see example in my last post).


Have a nice day
Sachs

Nils_Woehler

This is a bug that will be fixed with the next update. Normally the time should be set to 00:00:00.

Best,
Nils