Omniscope supports full Date & Time data typing, but unfortunately there are no international standards for formatting this type of data. Omniscope recognises most common Date & Time formats automatically on import, although Epoch/Unix formatted dates and times must be read in first as integer numbers (see below) [1]. If you create a new field (column) typed Date & Time, or if Omniscope does not initially recognise the formatting of your imported data, you may be asked to help Omniscope interpret the Date & Time formatting to facilitate the conversion, and also to specify how the imported values should be stored/displayed in Omniscope.
If Omniscope does not recognise a Date & Time column data formatting on import, it will probably initially data-type that column "Text", store the data as text values and display the column in the Table View with a white background, although sometimes the Date & Time formatting wizard will appear. Always confirm that all data which is supplied in the source data as Date & Time values has been successfully imported and typed Date & Time and therefore will be stored/displayed in Omniscope as full date/time values.
In both DataManager Field Organiser blocks or, if you have already loaded the data into the DataExplorer in memory data set, you can review and correct Date & Time data issues from the Main Toolbar: Data > Manage Fields data typing dialog:
Use the Date & Time formatting wizard if Omniscope needs help detecting the format of existing data, or to change a column's Date & Time display format to another. If you specify the time zone, it is important to specify the time zone and daylight-savings time settings of the database server recording the data, which may be different from your own. It is possible for server clocks not respecting daylight savings time to record data inconsistent with the time zone setting you have selected. You must also respect leap year dates, and not enter non-existent dates. Invalid times or dates will NOT be coverted. (See below for further discussion of time zone and Daylight Savings Time-DST issues).
Spreadsheets often use two different columns to separate the calendar day-month-year Date from the clock hours:minutes:seconds Time. It is usually best to combine these two fields on import to Omniscope and store/display them within Omniscope as fully-formatted dates and times; dd/MM/yyyy HH:MM:ss. You can display any part of the Date/Time according to the display formatting you specify, but Omniscope's Java date/time arithmetic functions require the value to be stored fully-specified.
WARNING: When specifying date formats, make sure your data in the field/column is uniform. For example, if some cell values have Hours:Minutes:Seconds after the date, and you want to preserve this format in Omniscope, make sure that ALL cell values in that field have entries for Hours:Minutes:Seconds. If necessary, you can pad them out with all zero values like this: 00:00:00. Also, if you are using Date & Time fields as criteria for Omniscope merge file joins, make sure the fields are set to exactly the same format. If you intend to use AM/PM please make sure that your time is in 12 hour clock rather than in 24 hour clock format.
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:
Symbol | Meaning | Examples | Notes |
Dates: | |||
yyyy | Year | 2002, 53, 1997, 500 BC | Literal year values, where "53" and "1066" will mean the years 53 and 1066 AD, and "200 BC" means the year 200 BC. |
yy | Year | 02, 53, 97 | Two-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+. |
w | Week | 1, 3, 52 | Week 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+) |
MMMM | Month | March, December | Full month name |
MMM | Month | Mar, Dec | Abbreviated month name |
MM | Month | 03, 12 | Two-digit month number, padded with zero |
M | Month | 3, 12 | One- or two-digit month number, no padding |
dd | Day of month | 03, 16 | Two-digit day in month, padded with zero |
d | Day of month | 3, 16 | One- or two-digit day in month, no padding |
EEEE | Weekday | Tuesday | Full name of day in week |
EEE | Weekday | Tue | Abbreviated name of day in week |
Times: | |||
aa | AM/PM | AM, PM | Use aa for the AM/PM marker |
HH | Hour (24) | 00, 07, 15, 23 | Hour of day in 24-hour clock, from 0 to 23, padded with zero |
H | Hour (24) | 0, 7, 15, 23 | Hour of day in 24-hour clock, from 0 to 23, not padded |
hh | Hour (12) | 01, 07, 11, 12 | Hour of day in 12-hour clock, from 1 to 12, padded with zero |
h | Hour (12) | 1, 7, 11, 12 | Hour of day in 12-hour clock, from 1 to 12, not padded |
mm | Minutes | 00, 09, 23, 59 | Minutes past the hour, padded with zero |
m | Minutes | 0, 9, 23, 59 | Minutes past the hour, not padded |
ss | Seconds | 00, 09, 23, 59 | Seconds, padded with zero |
s | Seconds | 0, 9, 23, 59 | Seconds, not padded |
SSS | Milliseconds | 000, 009, 023, 595 | Milliseconds, padded with zero |
S | Milliseconds | 0, 9, 23, 595 | Milliseconds, 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:00 | ISO 8601 timezone, alternative ':' separated format, or 'Z' for UTC |
dd-MM-yy | for values like: | 21-11-99 | meaning 21-11-1999 |
yyyy.MM.dd | for values like: | 2001.07.04 | |
yyyy.MM.dd HH:mm | for values like: | 2001.07.04 23:55 | |
EEE h:mm a | for 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 |
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.
The DATETOTEXT and TEXTTODATE functions allow you to specify time zones. Valid formats are as follows:
"GMT-08:00" or "GMT-8".
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.
Links:
[1] http://kb.visokio.com/book/export/html/359#epoch
[2] http://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html