CURRENT DATE Issue

user02830
user02830 Member
in Archive

I have an issue with the CURDATE() function. I have the following beast mode:

CASE WHEN
`Closing Date` < CURDATE()
THEN 'Past'
ELSE 'Current'
END

 

I use this beast mode to not sure the incomplete data from the current day (so I filter on Current).

 

However, the data for the previous day doesn't update until 11am. Our timezone for the company is correct (Melbourne, Australia) but instead of updating at the right time at the beginning of the day for the previous day, it waits until 11am. I know this is the issue as by removing this, the data for the previous day appears.

 

Any help would be appreciated.

 

Thanks!

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Try this out.  This will check the current time and if it is after 11:00 it will say that data for today is "Current", if it is before 11:00 it will say that yesterday's data is "Current"

     

    CASE WHEN

    NOW() >= CONCAT(CURDATE(),' 11:00:00') THEN

    CASE WHEN
    `Closing Date` < CURDATE()
    THEN 'Past'
    ELSE 'Current'
    END

    ELSE CASE WHEN

    `Closing Date`<CURDATE()-1

    THEN 'Past'

    ELSE 'Current'

    END


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    oops, I think that I missed an extra END on that last SQL statement


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02830
    user02830 Member

    Hi Scott,

     

    Thanks for the proposed solution. However, what I would like is not for the information to be updated at 11am but in the morning on the day of when it should update. 

     

    I.e. at the moment the data for the previous day doesn't update until 11am. I would like it to update at 00:00. I think the solution you have proposed would mean it would update at 11am correct? I would like it to update earlier.

     

    Thanks,

    Evan

  • [Deleted User]
    [Deleted User] Member

    Have you had a look at this article?

  • user02830
    user02830 Member

    Hi Dani,

     

    Thanks for the link to the article. Unfortunately it is doesn't help solve my problem. 

     

    The datasets are updating and as I wrote above the timezones are fine. The problem is the beast mode calculation as I mentioned in the start. If I remove it everything is fine.

     

    Does anyone know why the CURDATE() function is not getting the right time? Could it be set to UTC time or something?

  • Gimli
    Gimli Domo Employee

    Hello @user02830, 

    Thank you for the awesome question. 

    Currently Date/Time functions in beastmodes and Dataflows return the Server time. All of the Domo servers run in UTC time. This is to promote consitancy. 
    Using a function similar to CURDATE() or NOW() will return the date/time in a UTC format. 
    You can easily work around this by using a SUBDATE() function. For example: 
    SUBDATE(NOW(), INTERVAL x HOUR) 
    This will convert my date/time to my time zone based on the x hour I define. 

    I recoomend posting an article to our Idea Exchange section of dojo. Our product team sees all of these and will be able to look into the potential of creating a date funtion that returns the date/time in your timezone set in your company settings. 

    I hope you have a wonderful rest of your week!
    Cheers.

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories