Home Products Downloads Clients Resources Support Company  
Tutorials User Guide Maps Library Merge Files Web Services Image Sets Graphics
Home » Resources » User Guide » Configure Data

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:

SymbolMeaningExamplesNotes
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.
w
Week1, 3, 52
Week in year
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
SymbolMeaningExamplesNotes
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

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