Skip to content

    • Magic ETL

    Magic ETL

    Why does the DATE() function return the next date after 6:00 PM?

    Domo Employee
    in Magic ETL

    I have a dataset with a Date and Time stamp field.  When I convert it to a date field using DATE(`date_field`) I am finding some unexpected results.

    1.png

     

     

    The only way that I was able to get the intended results was to subtract 6 hours from the timestamp first:

    Is this an intended function of the DATE() function, or am I doing something wrong?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      Answer ✓

      Have you tried using the Convert_TZ() fundtion  as a second argument ?

      here it is an simple example of how to use it

       

      DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)

      this give us the number of days from current date/time

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Coach
      Answer ✓

      The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names

       

      for example:

      1. CONVERT_TZ(`Date`, '+00:00', '+10:00')

        CONVERT_TZ(`Date`,'UTC','American/Chicago')
      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Contributor
      Answer ✓

      Try this one out.

      convert_tz(`Date and time of Last Change`,'utc','us/central')


      -----------------
      Chris

    Answers

    • Coach

      It does the same thing on my flows so we have to adjust for UTC time since we are in EST time,  always substracting 4 or 5 hours depending of daily savings time to prevent that issue

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Domo Employee

      How do you determine the number of hours to subtract though?  I would like to prevent the need to change my dataflow every 6 months or so

    • Coach
      Answer ✓

      Have you tried using the Convert_TZ() fundtion  as a second argument ?

      here it is an simple example of how to use it

       

      DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)

      this give us the number of days from current date/time

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Domo Employee

      I feel like this runs into the same issues.  At some point you would need to change from 

      1. CONVERT_TZ(now(),'UTC','EST')

      to

      1. CONVERT_TZ(now(),'UTC','EDT')

      I can't find a way to automate the switch, given that the actual date changes each year

    • Coach
      Answer ✓

      The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names

       

      for example:

      1. CONVERT_TZ(`Date`, '+00:00', '+10:00')

        CONVERT_TZ(`Date`,'UTC','American/Chicago')
      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Domo Employee

      I thought we had a solution here, but I'm not able to get the long timezone name to work:tz.png

       

    • Contributor
      Answer ✓

      Try this one out.

      convert_tz(`Date and time of Last Change`,'utc','us/central')


      -----------------
      Chris
    • Domo Employee

      Thank you @cwolman  and @Godiepi 

       

      I'm not sure how many answers I can accept as a solution, but I've got this conversion working now.  

      ty sman.png

       

    • Coach

      @ST_-Superman-_  its got to take more than one to be able to help the man of steel ?  (Superman theme song playing in the backgorund)

       

      @DaniBoy  should take @cwolman  and me up a Belt in the Dojo for doing this LOL !

       

      Have a good day guys

      Domo Arigato!

      **Say 'Thanks' by clicking the thumbs up in the post that helped you.
      **Please mark the post that solves your problem as 'Accepted Solution'
    • Member

      This is an AWESOME example of community collaboration!

      Well done @Godiepi @cwolman  and @ST_-Superman-_ 

       

      Enjoy your new belts! You guys are all Super!

      Cheers!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories