"Result of date type attributes in Write Excel operator"

qwertzqwertz Member Posts: 130 Contributor II
edited June 2019 in Help

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
Tagged:

Answers

  • Nils_WoehlerNils_Woehler Member Posts: 463 Maven
    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
  • qwertzqwertz Member Posts: 130 Contributor II

    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_WoehlerNils_Woehler Member Posts: 463 Maven
    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

  • qwertzqwertz Member Posts: 130 Contributor II

    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_WoehlerNils_Woehler Member Posts: 463 Maven
    This is a bug that will be fixed with the next update. Normally the time should be set to 00:00:00.

    Best,
    Nils
Sign In or Register to comment.