Date formats in Omniscope

Modified on Wed, 7 Dec, 2022 at 2:12 PM


A date format is a user-specified sequence of case-sensitive characters describing the format of date/time values. For example, to show your dates as "16-Mar-2002" you would use the date format "dd-MMM-yyyy". You can use any punctuation, but letters must be in one of the valid patterns of characters listed below:

SymbolMeaningExamplesNotes

Dates: 

   
yyyyYear2002, 53, 1997, 500 BCLiteral year values, where "53" and "1066" will mean the years 53 and 1066 AD, and "200 BC" means the year 200 BC.
yyYear02, 53, 97Two-digit years, with Y2K fix, where "53" and "10" will mean the years 1953 and 2010. Assumes any two-digit years fall within the last 80 years or next 20 years.
YYYY Week year 2012 As per yyyy, except this is the "week year", i.e. the year of the current week-in-year, which is not always the same for a few days in the first and last weeks of the year.
Only available in Omniscope installations based on Java 7, typically 2.8+.
wWeek1, 3, 52Week in year
Warning: week of year yields different results in different locales, due to different regional settings for first-day-in-week.  
See Settings > Advanced file settings > Regional Settings (2.8+)
MMMMMonthMarch, DecemberFull month name
MMMMonthMar, DecAbbreviated month name
MMMonth03, 12Two-digit month number, padded with zero
MMonth3, 12One- or two-digit month number, no padding
ddDay of month03, 16Two-digit day in month, padded with zero
dDay of month3, 16One- or two-digit day in month, no padding
EEEEWeekdayTuesdayFull name of day in week
EEEWeekdayTueAbbreviated name of day in week

Times:

   
aaAM/PMAM, PMUse aa for the AM/PM marker
HHHour (24)00, 07, 15, 23Hour of day in 24-hour clock, from 0 to 23, padded with zero
HHour (24)0, 7, 15, 23Hour of day in 24-hour clock, from 0 to 23, not padded
hhHour (12)01, 07, 11, 12Hour of day in 12-hour clock, from 1 to 12, padded with zero
hHour (12)1, 7, 11, 12Hour of day in 12-hour clock, from 1 to 12, not padded
mmMinutes00, 09, 23, 59Minutes past the hour, padded with zero
mMinutes0, 9, 23, 59Minutes past the hour, not padded
ssSeconds00, 09, 23, 59Seconds, padded with zero
sSeconds0, 9, 23, 59Seconds, not padded
SSSMilliseconds000, 009, 023, 595Milliseconds, padded with zero
SMilliseconds0, 9, 23, 595Milliseconds, not padded
X Timezone -07, Z, +10 

ISO 8601 timezone, hours only (offset) or 'Z' for UTC 

XX Timezone -0700, Z, +1000 ISO 8601 timezone, hours and minutes (offset) or 'Z' for UTC  
XXX Timezone  -07:00, Z, +10:00ISO 8601 timezone, alternative ':' separated format, or 'Z' for UTC  


Example patterns:

dd-MM-yyfor values like:21-11-99meaning 21-11-1999
yyyy.MM.ddfor values like:2001.07.04 
yyyy.MM.dd HH:mmfor values like:2001.07.04 23:55 
EEE h:mm afor values like:Sat 9:33 PM 
yyyy-MM-dd'T'HH:mm:ss.SSSZ  for values like:2001-07-04T12:08:56.235-0700  
yyyy-MM-dd'T'HH:mm:ss.SSSXXX  for values like: 2001-07-04T12:08:56.235-07:00  
For a further information see the full Java documentation, including additional date pattern symbols such as "D" for "day of year".

Time Zone Issues

Databases record data using the time zone specified for that database. The database could be located anywhere in the world. In addition, the database may or may not reflect daylight savings time adjustments, even locally. If the data in the data set does not reflect daylight savings time, i.e. runs on GMT, and you select BST British summer time as a time zone for your data, Omniscope (and Java) will report invalid dates/times for some records. This occurs when daylight savings time adjustments 'skip over' one hour after midnight on specific dates. To fix this problem, you must isolate the offending times, and manually advance them by 1 hour.

Specifying time zones 

The DATETOTEXT and TEXTTODATE functions allow you to specify time zones.  Valid formats are as follows:

"GMT-08:00" or "GMT-8".

Importing Epoch/UNIX/POSIX dates & times

Epoch (or UNIX or POSIX) time is a system for describing points in time, defined as the number of (milli) seconds elapsed since midnight Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds. Omniscope will import dates and times in this format if they are first read in as integer numbers. Once your data is imported as integer, in Data > Manage fields change the data type to Date & Time and select the option at the bottom Convert milliseconds since 1970 into dates. Omniscope uses milliseconds, so if your data is in seconds, multiply the values by 1000 using a formula column, then specify the Date & Time display format you wish to use.


Troubleshooting : publishing date formats as CSV - opening in Excel

If you're creating File outputs in csv format that will be consumed by your clients or colleagues in different regions

you wish to check if the date values are interpreted correctly on the other end, when opened in Ms Excel.

(Remember that 07/12/2022 and 12/07/2022 are ambiguous).


The solution is to use the "reverse date format": 2022-12-07. Configure your date fields in Omniscope to use the yyyy-MM-dd format pattern (not perfect in terms of readability), but you might be able to live with this in your reports), and any CSV files you export will open in Excel correctly, regardless of locale. 

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article