4.5 Formats for Data and Time Attributes
Many entities in the data model have attributes that relate to date and time (eg. the start and completion of building of a particular construction_element within a groundwater_feature).
| The
standard format for expressing date and time is DD-MON-YYYY-HH24:MI:SS |
| where | DD is the day of the month eg. 06 | |
| MON is the three-letter abbreviation for the month | ||
|
JAN
= January
|
||
|
FEB
= February
|
||
|
MAR
= March
|
||
|
APR
= April
|
||
|
MAY
= May
|
||
|
JUN
= June
|
||
|
JUL
= July
|
||
|
AUG
= August
|
||
|
SEP
= September
|
||
|
OCT
= October
|
||
|
NOV
= November
|
||
|
DEC
= December
|
||
|
YYYY
is the year eg. 1998
|
||
|
HH24
is the hour based on the 24-hour clock eg. 22
|
||
|
MI
is the minute eg. 46
|
||
|
SS
is the second eg. 35
|
||
| eg. |
23-JAN-1998-12:30:53
|
|
|
15-APR-2003-18:46:33 |
||
Note that the local time zone (eg. Australian Eastern Standard Time, Australian Eastern Summer Time) operating at the time of recording is used.
As is the case for most relational databases, the date datatype requires the complete storage of the second, minute, hour, day, month and year. In most cases, the timing of events is only known to the nearest day, or even worst to the nearest month or year. It may only be known that the event occurred after or before a particular date. Hence, the date_reliability attribute is used to describe how the date and time should be treated. The domain for the date_reliability attribute is presented in Table 4.3.
The values that are used if components of the date and time are not known are:
|
JAN
for month
|
|
00
for seconds
|
|
00
for minutes
|
|
00
for hours
|
|
01
for day
|
|
0000
for year
|
Hence, if the time of day is not known (ie. the HH24:MI:SS component), then 12 am (midnight, 00:00:00) at the beginning of the day is used as the default. If the day is not known, then the first day of the month is used (eg. 01), and if the month is not known, then January (JAN) is used. For example, if it is only known that a bore was constructed in 1936, then the date attribute would be
‘01-JAN-1936-00:00:00’ and the date_reliability would be ‘YR’.
Table 4.3 Domain for the date_reliability attribute
|
Code |
Category |
Description |
|
EST |
estimate |
date and time only an estimate |
|
BSEC |
before second |
before the second of the minute of the hour of the day |
|
BMIN |
before minute |
before the minute of the hour of the day |
|
BHR |
before hour |
before the hour of the day |
|
BDAY |
before day |
before the day |
|
BMON |
before month |
before the month |
|
BYR |
before year |
before the year |
|
SEC |
known second |
known to the second of the minute of the hour of the day |
|
MIN |
known minute |
known to the minute of the hour of the day |
|
HR |
known hour |
known to the hour of the day |
|
DAY |
known day |
known to the day |
|
MON |
known month |
only known to the month |
|
YR |
known year |
only known to the year |
|
ASEC |
after second |
after the second of the minute of the hour of the day |
|
AMIN |
after minute |
after the minute of the hour of the day |
|
AHR |
after hour |
after the hour of the day |
|
ADAY |
after day |
after this day |
|
AMON |
after month |
after this month |
|
AYR |
after year |
after this year |
|
ERR |
error |
date and time possibly in error |
|
UNK |
unknown |
reliability of date is unknown |
A days:hours:minutes:seconds format is used for depicting time increments, such as the duration of an aquifer test or construction activity. For example, a time length of 1.5 hours is depicted as 0:1:30, a time length of 60 hours is depicted as 2:12, and a time length of 23 seconds is depicted as 0:0:0:23.
|
