Skip to main content

Odbcloader and date/time/timestamp columns

  • April 6, 2016
  • 3 replies
  • 22 views
rok
Forum|alt.badge.img
  • rok
  • Participating Frequently

Hi There,

 

I am using odbcloader with Informix IDS and it works great - for the most part. The only issue I could find so far is that date/time/timestamp columns in the source-db seem to be considered GMT and then converted to local timezone. In my case, this yields a shift of -1 hour (I am living in Germany) . I do not knot whether it is possible to specify the local timezone to odbc, but in my opinion a date/time/timestamp values should simply be copied to the same value in vertica. I could figure out why that happens and would like to share a workaround/fix. Does anybody know how to contact the maintainer(s) ?

 

Thanks in advance

    Ralf

    3 replies

    rok
    Forum|alt.badge.img
    • rok
    • Author
    • Participating Frequently
    • April 6, 2016

    PS: in my opinion, this should be a general probmle and not Informix specific. Does anybody have the same issue with a different database ?

    Itipong_Chewinp
    Forum|alt.badge.img
    • Itipong_Chewinp
    • Participating Frequently
    • September 27, 2016

    Use timegm() instad of mktime() in ODBCLoader.cpp.

     

    Line 293: writer->setDate(i, getDateFromUnixTime(mktime(&d)));

    Line 293: writer->setDate(i, getDateFromUnixTime(timegm(&d)));

     

    Line 305: writer->setTimestamp(i, getTimestampFromUnixTime(mktime(&d)) + s.fraction/1000);

    Line 305: writer->setTimestamp(i, getTimestampFromUnixTime(timegm(&d)) + s.fraction/1000);

     

    timegm is functionally identical to mktime except it always takes the input values to be Coordinated Universal Time (UTC) regardless of any local time zone setting.

    Note that timegm is the inverse of gmtime.

    Portability note: mktime is essentially universally available. timegm is rather rare. For the most portable conversion from a UTC broken-down time to a simple time, set the TZ environment variable to UTC, call mktime, then set TZ back.

     

    Refer: https://www.gnu.org/software/libc/manual/html_node/Broken_002ddown-Time.html

    rok
    Forum|alt.badge.img
    • rok
    • Author
    • Participating Frequently
    • September 27, 2016

    Thank you very much for the answer. That's exactly what I did, but I would rather have contacted the maintainer(s) (in case it's still maintained) since I thought, that's a general problem and people who don't participate here could profit from a fix as well. Portabilty should not really be an issue, since Vertica runs exclusively on Linux.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement