Dates & Times
Working with Dates & Times
Omniscope supports Date & Time data typing. Most common Date & Time formats are recognised automatically on import. If you create a new field (column) of this type, or Omniscope does not recognise the format in your imported data set, you may be asked to provide guidance on interpreting the format, and to specify how the imported values should be displayed. The Date & Time formatting wizard will appear, or can be accessed from the Edit > Manage Fields dialog whenever changing a data type to Date & Time:
Use the Date & Time formatting wizard if Omniscope needs help detecting the format of existing data, and to change that 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 select. (See below for further discussion of time zone and Daylight Savings Time-DST issues).
WARNING: When specifying date formats, make sure your data 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.
Help With Date Formats
A date format is a 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 |
|---|---|---|---|
| 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. |
| w | Week | 1, 3, 52 | Week in year |
| 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 |
| Symbol | Meaning | Examples | Notes |
| 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 |
Example patterns:
| 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 |
- 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, but 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.
Back to Configure your Data
User Guide Top