Forum Discussion

virender_prajap's avatar
virender_prajap
New Contributor III
7 years ago
Solved

BUG OR ERROR : Date UTC Method

Hi All,

I am using Date.UTC() method to generate last month date & using below syntax which seems to be fine.

Date.UTC(Date.now().minusMonths(1).getFullYear(),Date.now().minusMonths(1).getMonth(),0)

And output preview is also fine.

But while I am inserting data into Orcale DB, 1 day is getting subtracted from the date.

Not able to figure out where is the issue. Tried by changing date data type to varchar2 at DB level…but the value remain same.

  • jaybodra's avatar
    jaybodra
    7 years ago

    @virender.prajapati I have seen this happening to me in MySQL. I used toLocalDateTimeString() function before inserting it into the DB and worked fine.

    You can try this in your mapper,
    Date.UTC(Date.now().minusMonths(1).getFullYear(),Date.now().minusMonths(1).getMonth(),0).toLocaleDateTimeString({“format”: “yyyy-MM-dd HH:mm:ss”,“timeZone”:“UTC”})

    You can also look into oracle jdbc url properties

6 Replies

  • tstack's avatar
    tstack
    Former Employee
    7 years ago

    Looks like there is some timezone conversion being applied. The time in the preview is “00:00:00”, while the time in the other screenshot is “20:00:00”. Is the value being converted at some other point in the pipeline or oracle?

    • virender_prajap's avatar
      virender_prajap
      New Contributor III
      7 years ago

      In pipeline data value remain same before the oracle insert snap, value is getting changed in Oracle DB.
      But ideally it should insert value passed by pipeline only. Seems like in Oracle insert Snap…oracle timestamp is used which is generating this kind of problem.

  • jaybodra's avatar
    jaybodra
    New Contributor III
    7 years ago

    @virender.prajapati I have seen this happening to me in MySQL. I used toLocalDateTimeString() function before inserting it into the DB and worked fine.

    You can try this in your mapper,
    Date.UTC(Date.now().minusMonths(1).getFullYear(),Date.now().minusMonths(1).getMonth(),0).toLocaleDateTimeString({“format”: “yyyy-MM-dd HH:mm:ss”,“timeZone”:“UTC”})

    You can also look into oracle jdbc url properties