Dates & Times

Working with Dates & Times

Omniscope supports Date & Time data typing. Most common Date & Time formats are recognised automatically on import, although Epoch/Unix format dates and times must be read in first as integers (see below). If you create a new field (column) typed Date & Time, or if Omniscope does not recognise the format your imported data is in, 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 Data > 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, 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 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 Formatting

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, 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.

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.

Back to Configure your Data

User Guide Top

Printer-friendly version
Login to post comments

Omniscope User Guide