Date Field Added 1 Day To Itself.

Sweep_The_Leg
Sweep_The_Leg Member
in Connectors

I have a date field that includes the time stamp. I used the CAST to simplify the date and rmeove the time stamp. When I do that, the date increases by 1 day. Any help? See attached 

Best Answer

  • DDalt
    DDalt Member
    Answer ✓

    This may be occuring if you have your Company Time Zone Settings set to your local time.

     

    If this is the case, then when you begin using native SQL commands, it treats the time within those commands at UTC time. If you are West of the Central Time Zone then you are at least -7 hours from UTC, so in your example the system is probably reading your original date as November 27, 12:23 AM and casting that timestamp as DATE.

     

    Your solution will probably be to ensure that you are converting your timestamp from UTC to Local Time (or vice versa if I have this backwards) and then casting that as a date:

     

    CAST(CONVERT_TZ(`date_created`,'UTC','MET') AS DATE) AS `Date_Created`


    Note: You will need to look up the abbreviation for your time zone.

     

    Extra Note: Try running the code without the CAST to see how the times are being converted. You may want to run the data flow completely to see what happens to the data when it displays in the output data set 

Answers

  • DDalt
    DDalt Member
    Answer ✓

    This may be occuring if you have your Company Time Zone Settings set to your local time.

     

    If this is the case, then when you begin using native SQL commands, it treats the time within those commands at UTC time. If you are West of the Central Time Zone then you are at least -7 hours from UTC, so in your example the system is probably reading your original date as November 27, 12:23 AM and casting that timestamp as DATE.

     

    Your solution will probably be to ensure that you are converting your timestamp from UTC to Local Time (or vice versa if I have this backwards) and then casting that as a date:

     

    CAST(CONVERT_TZ(`date_created`,'UTC','MET') AS DATE) AS `Date_Created`


    Note: You will need to look up the abbreviation for your time zone.

     

    Extra Note: Try running the code without the CAST to see how the times are being converted. You may want to run the data flow completely to see what happens to the data when it displays in the output data set 

  • Sweep_The_Leg
    Sweep_The_Leg Member

    Thanks for the info. I tried all kinds of variations and even MySQL but just can't get it to work. Anything with a time stamp after 5:00PM in my records gets converted to the next day. I'll try a beastmode and see if I can trim it down there. Idealy I always like to do as much as I can in the underlying query. 

     

    Thanks again!

  • DDalt
    DDalt Member

    I think your bet bet to figure it out is to continue to experiment with additonal columns to figure out where the system is changing your dates. Check to see what happens when you write:

     

    CAST(a.date_created as datetime) as 'Date Set'

     

    and then run the data flow. This should give you a hint as to what happens when you use the CAST function in a MySQL data flow. If it returns the exact same time as your a.DATE_CREATED then surely you can just just wrap it into this:

     

    ,DATE(CAST(a.date_created as datetime)) as 'Date Set'

     

    If the CAST as datetime statemtent returns something like November 27, 12:23:56 AM then you know there is some converting that needs to be done. It may even require a two-step data transform where you convert in the first transform, and then CAST as DATE or write DATE('date set') in a second transform.

     

    I am so bummed because I must have deleted my timezone notes for this behavior after I switched our company time zone settings to UTC. It also means I can't exactly replicate the problem but hopefully this can be a nudge in the right direction.

     

     

Categories