Best Of

Hello Domo users! As a relative newcomer to this fantastic platform, I am thrilled to share my first discussion post with the greater Domo Community. I've been working with Domo for nearly a year at this point, and it has been a wonderful experience and I truly admire the insightful analytics and operational abilities this platform is able to provide to its users.

Over the past few months, I have learned how important it is to properly present and format dates and timestamps to provide insightful analysis and present accurate and impactful representations of data. Whether it is a minor use case or a monumental project, this is a reality that everyone will experience in any situation when presenting data. Today, I would love to demonstrate to the Domo community how exactly we can utilize the custom date & timestamp formatting abilities that can be accomplished in Domo.

I learned that the patterns used for custom date formatting in this view are based off the DateTimeFormatter class in Java. While there is documentation available for the DateTImeFormatter class in Java, I would like to provide specific examples that anyone can use in their own use cases in Domo. To illustrate this, I've experiemented with various examples based on the following timestamp: Tuesday, March 14, 2023 03:09:26 PM

Examples:

• Full date with day of the week, month, day, year, timestamp in 12 hour AM/PM format, and AM/PM distinction: EEEE, MMMM dd, yyyy hh:mm:ss a (Ex: Tuesday, March 14, 2023 03:09:26 PM)
• Full date with day of the week, month, day, and year: EEEE, MMMM dd, yyyy (Ex: Tuesday, March 14, 2023)
• Date and time in the format "yyyy-MM-dd HH:mm:ss": yyyy-MM-dd HH:mm:ss (Ex: 2023-03-14 15:09:26)
• Short date format "MM/dd/yyyy": MM/dd/yyyy (Ex: 03/14/2023)
• Year and month: yyyy-MM (Ex: 2023-03)
• Abbreviated day of the week and short month name: E, MMM dd (Ex: Tue, Mar 14)
• Custom pattern for day of the week, day, month, and year: E, d MMM yyyy (Ex: Tue, 14 Mar 2023)

KEY:

Date Shorthand:

• L (equivalent to "Date Shorthand" date format) (Ex: 03/14/2023)

Quarter:

• Q: Fiscal quarter in numeric format (Ex: 1)

Week:

• W or w: Week in numerical format (Ex: 11)

Day of Week:

• e: Localized day of week (Ex: 2)
• E: Short-hand day of week (Ex: Tue)
• EEEE: Full day of week (Ex: Tuesday)

Day:

• d: Day in one or two digits (Ex: 14, or 5 if the date was March 5th)
• D or dd: Day in two digits (Ex: 14, or 05 if the date was March 5th)

Month:

• M: Month in one digit (Ex: 3)
• MM: Month in two digits (Ex: 03)

Year:

• y: Year in two digits (Ex: 23)
• Y or yyyy: Year in four digits (Ex: 2023)

Hour:

• HH: Hour in 24 hour AM/PM format in two digits (Ex: 15, since 3 hours after 12pm is 3pm)
• k: Hour in 24 hour AM/PM format (Ex: 15)
• hh: Hour in 12 hour AM/PM format in two digits (Ex: 03)
• h: Hour in 12 hour AM/PM format in one digit (Ex: 3)

Minute:

• m: Minute in one or two digits (Ex: 9)
• mm: Minute in two digits (Ex: 09)

Second:

• s: Second of a minute in one or two digits (Ex: 26, or 9 if the timestamp was 03:09:09 PM)
• ss: Second of a minute in two digits (Ex: 26, or 09 if the timestamp was 03:09:09 PM)
• S: Fraction of a second (Ex: 246 if the millisecond part of the timestamp 03/14/2023 03:09:26 PM was 246)

AM vs. PM:

• a: AM or PM in timestamp in lower case (Ex: pm)
• A: AM or PM in timestamp in upper case (Ex: PM)

Offsets:

• Z: Time zone offset (compared to UTC) expressed as hours and minutes. (Ex: -05:00 for Central Standard Time (CST) to UTC cross-comparison since there is a 5-hour difference between those time zones)
• X: Unix timestamp (Ex: 1678824566, since 1,678,824,566 seconds have elapsed since January 1, 1970 00:00:00 UTC (the Unix epoch) given that the timestamp is March 14, 2023 03:09:26 PM in CST)

Please feel free to explore these formatting options in your analyzer view and leverage the incredible abilities Domo can provide to your use case! I am an active Domo user, so you are all welcome to respond with your comments and questions about this topic, and I'll be more than happy to make additional edits and provide assistance in any way I can!

@ST_-Superman-_ I've run into this a few times before. I have been able to resolve it by clicking the X to close Analyzer and it will work when I re-open it. Unfortunately, your changes will not be saved and you will have to redo it.

You can use some window functions in a beast mode to do this. Specifically the LAG function. Here's a post on how to do this: