AddUp's date and time functions process timestamp values expressed with the general format [YYYY-MM-DD HH:MM:SS] to represent the number of seconds elapsed since January 1st of a base year 0.
A timestamp is any plain number whose value represents the number of seconds elapsed since January 1st of a base year 0. The absolute value of such a number is not ordinarily useful by itself, but since it can accurately mark dates and/or times, it enables calculations that are compatible with the standard time unit (the second).
Find AddUp at Download.comTimestamp values can be constructed and displayed using date and time tokens within square brackets. The general format of a timestamp representation is [YEAR-MO-DY HR:MN:SC] where individual tokens are:
Note how date and time tokens are listed in decreasing order of magnitude: year first and seconds last. This order must be respected. For example, 3:30 in the morning of December 31st of the year 2000 is represented as [2000-12-31 03:30:00]. Note also that the default time format uses a 24-hour clock so the same time in the afternoon is [2000-12-31 15:30:00]. A number of format variations are recognized however and are discussed in the details section below.
Since timestamps are nothing more than a number of seconds, they appear as regular numbers unless a timestamp format is requested. For example, function now returns a timestamp for the current date and time of day. The unformatted output of this function would look like this:
now = 6.328572e+10
This value is not particularly useful to tell us the current date and time, but we can add an output modifier prefix to get a readable result: start the expression with a single quote, a pair of square brackets and a separating space before the timestamp value:
'[] now = [2005-06-11 14:40:13]
This prefix modifier tells AddUp to use a timestamp format when the result of an expression is shown. (If you have previously used AddUp to perform numeric base conversions, you are already familiar with the single quote notation that is also used for that purpose.) In this instance, the number of seconds returned by the now function is divided into separate date and time tokens for display.
You can display the current date without the current time by using only the left square bracket in the prefix. You can also display the current time without the current date by using only the right bracket:
'[ now = [2005-06-11] '] now = [14:40:13]
If a 12-hour clock is prefered over a 24-hour clock, add either the letter 'A' or 'P' to the prefix. Letter case or position to the left or right of the bracket are unimportant here, and both letters have the same effect in this context. A 12-hour clock will be used and the relevant letter will appear before the time portion of the timestamp to indicate either morning or afternoon. These letters have no effect on date-only output.
']p now = [P 02:40:13] '[A] now = [2005-06-11 P 02:40:13] 'a[ now = [2005-06-11]
Given a timestamp value, individual date and time tokens can be retrieved using get functions:
Since timestamps are simply a number of seconds, they can be used in
general calculations, and the result of these calculations can be converted
into various other time units using the "->
" conversion
operator: minutes, years, centuries or others. For example, the number of
days or weeks between [2010-01-01] and [1990-06-30] is obtained by
subtracting these timestamps and converting the resulting number of seconds
into the desired unit of time:
([2010-01-01] - [1990-06-30]) -> days = 7,125
([2010-01-01] - [1990-06-30]) -> weeks = 1,017.8571
([2010-01-01] - [1990-06-30]) -> years = 19.507587
Be sure to use parentheses before applying the conversion operator.
It is also possible to add or subtract various amounts of time to a timestamp in order to obtain a past or future timestamp. For example, three days and one quarter before or after a given date and time is obtained with:
'[] [2005-06-30 9:00] - 3 1/4 days = [2005-06-27 03:00:00] '[] [2005-06-30 9:00] + 3 1/4 days = [2005-07-03 15:00:00]
Notice from the examples above that when dates are subtracted from each other the result is a duration, it is not a date or a time of day. This duration is then expressed in the time unit of your choice instead of using seconds. On the other hand, when an amount of time is added to or subtracted from a date, the result is another date. Even though the result is still expressed in seconds, its interpretation is different since it is based on a starting date and time of [0000-01-01 00:00:00]. Date and time results are shown as timestamps using the '[] prefix.
Here are additional examples of date and time calculations:
'] [12:00:00] + 5 hours = [17:00:00] '[ [2000-01-01] - 180 days = [1999-07-05] ([13:15:59] - [09:42:10]) -> minutes = 213.81667 ([1999-12-31] - [2000-01-01]) -> hours = -24Find AddUp at Download.com
The notation used within timestamps is distinct from the notation used outside of them. This means that you cannot perform calculations inside a pair of square brackets, you can only use the date and time elements described here. Likewise, units and functions cannot be used, and the numeric base is always decimal.
For precise time measurements, timestamps can also include a number of
milliseconds. Milliseconds are always part of a timestamp but not are not
routinely displayed. To include milliseconds in a timestamp, add a decimal
mark after the second followed by exactly three digits. An error message is
issued if either more or less than 3 digits are used for milliseconds. To
show milliseconds in a timestamp, add the letter 'M' to the prefix
specifier. For example use 'M]
before an expression to show it
as a timestamp that includes milliseconds.
An alternate format can be used to represent a date using a year and the day of the year (from 1 to 366). The format is [YYYY-DDD] where:
Note that exactly three (3) digits must be used for the day otherwise they will be treated as a month value instead. Leading zeroes must be used to pad day values less than 100. Function doy (dayOfYear) can be used to retrieve the day of the year for a timestamp value.
Date tokens are normally separated from each other using dashes (or hyphens). Time tokens are separated from each other using colons. These punctuation marks are useful to make timestamps more readable and to relax the need to display leading zeroes at each element: [0123-04-05] can be represented as [123-4-5] when punctuation is used, and the same principle holds true for dates.
Punctuation marks can also be used as simple placeholders for the current date or time tokens in the absence of an explicit value. For example, [2000--] is a timestamp value that represents the current month and day in the year 2000, while [-06-15] represents June 15 of the current year and [15::.] represents the current minute, second and millisecond at 3 PM. When a timestamp is evaluated, current time tokens are substituted for any absent one. In the extreme, the current date and time value can be obtained with timestamp [--::.] which is equivalent to calling the now function.
Trailing portions of date and/or time can also be removed completely and made to default to the base [0000-01-01 00:00:00.000] token values. For example, timestamp [1999-09 12:34] equates [1999-09-01 12:34:00.000] and timestamp [1999 12] equates [1999-01-01 12:00:00.000]. Without an explicit value and without any punctuation to say that current time tokens are to be used, base values are used instead. Note that when a timestamp contains a single number, the number is assumed to be a year: timestamp [20] means date [0020-01-01] and not time [20:00:00]. Add a 'T' prefix before a lone hour to signal a time instead: [T20] refer to [20:00:00] or 8 PM. To use a 12-hour clock, replace 'T' by either 'A' or 'P' so that [A8] and [P8] mean [08:00:00] and [20:00:00] respectively.
Timestamps can also be read correctly if punctuation is removed entirely as long as the supplied digits follow a defined pattern: 4 digits for the year, optionally followed by either:
Timestamps [2000123] and [20001231] represent the 123rd day of 2000 and December 31 2000, respectively.
For times, the pattern is 2 digits for the hour, optionally followed by 2 digits for the minutes, optionally followed by 2 digits for the seconds, optionally followed by 3 digits for the milliseconds.
When punctuation is not used and a date is not included before the time, it is necessary to use the 'T' prefix to ensure that the value is treated as a time and not a partial date. If a 12-hour clock is used instead then an 'A' or 'P' prefix can replace 'T' to specify morning or afternoon.
When punctuation is not used and the date is not expressed with eight characters exactly (either a 3-digit day-of-year format is used, or the day and/or the month value are omited) it is necessary to use the T prefix (or A or P) to mark the start of the time digits.
Users of the Gregorian calendar commonly use the convention that the year preceding 1 AD was 1 BC, jumping over year zero. Since AddUp only processes dates AD, and since some users may have a need for an actual year 0, the calender used here is extended to include a year 0. This makes [0000-01-01 00:00:00.000] the base of all timestamp values.
Historically, when the Gregorian calendar replaced the less accurate Julian calendar, ten days of the old calendar were skipped over in order to catch up with accumulated astronomical drift caused by a Julian year that was a little longer than the earth's revolution. These "missing" days do not occur in a strictly Gregorian calendar that extends back in time well before its official adoption. All past dates are valid all the way back to year 0 using this extended calendar.
Non-leap years have 365 days while leap years have 366 (where February has 29 days instead of 28). Here is the Gregorian calendar rule used to determine if a year is a leap year:
Function leap can be used to determine if a timestamp occurs within a leap year or not.
Time keeping has become an extremely precise science. The rotation of the earth was once used as the basis for the definition of a second, but this definition is now based on atomic clocks which are even more precise. Modern measurements show that the earth's rotation it not always uniform but can occasionally vary due to geological phenomena: tectonic plate shifts, changes in the ice caps, etc. These unpredictable changes are reflected in an occasional "leap second" that is either added or subtracted from the standard atomic time, mainly for the benefit of astrophysical sciences. Since these time adjustments are very small and since they do not follow a well-defined pattern that can be reproduced in software, leap seconds are not accounted for by the calculator. It is expected that those with a specialized need to track leap seconds already have a table of these adjustments on hand and can include them in their time calculations as needed.
While morning and afternoon times are usually well understood, the terms noon and midnight themselves are not exactly defined. Some people consider midnight to be 12 AM and noon to be 12 PM, others consider the opposite, and many others never gave it a second thought. In fact, the ISO committee on date and time standards recommends not to use these terms at all but to use the unambiguous 24-hour clock instead. For the purpose of using the 12-hour clock, the following equivalencies are followed:
| Time of day | 24-hour clock | 12-hour clock equivalent |
|---|---|---|
| AM - Midnight | [00:00:00] | [A 12:00:00] or [A 00:00:00] |
| AM | [00:59:59] | [A 12:59:59] or [A 00:59:59] |
| AM | [01:00:00] | [A 01:00:00] |
| AM | [11:59:59] | [A 11:59:59] |
| PM - Noon | [12:00:00] | [P 12:00:00] or [P 00:00:00] |
| PM | [12:59:59] | [P 12:59:59] or [P 00:59:59] |
| PM | [13:00:00] | [P 01:00:00] |
| PM | [23:59:59] | [P 11:59:59] |
Depending on local legislation and on the time of the year, your operating system may implement Daylight Saving Time (DST) by adding a bias to your local system clock. This bias is commonly 60 minutes applied during the summer months, but not all regions implement this, not all at the same time and the bias is not necessarily one hour.
AddUp does not itself implement DST but it queries the operating system for this value. Function dst returns the current bias as reported by the operating system, and functions that generate timestamps (date, now and time) provide local times after adjusting for DST.
Standard (non-DST) time can be obtained from a local timestamp by removing its DST bias, that is by subtracting dst from the timestamp value. A shortcut to this subtraction is to use the letter S (as in Standard) in the output prefix to signal that the value should be shown using standard time instead. Expression 's] [12:00] is equivalent to '] [12:00] - dst.
Conversely, an arbitrary timestamp can be expressed in standard time by
adding the value of dst to it. A shortcut notation
that has the same effect is to append suffix S at the end of the
timestamp, just before the closing bracket, to indicate that the time uses
the standard clock. Timestamp [12:00 S]
is an equivalent shortcut
notation for [12:00] + dst
.
Depending on your location, your operating system may apply a time bias to your system clock in order to provide local time. This bias is based on your time zone. Its value should be negative in the western hemisphere and positive in the eastern hemisphere. Function tz A retrieves the time zone bias reported by the operating system.
Also known as Zulu time and formerly named Greenwich mean time (GMT), coordinated universal time is the international basis for time keeping. It is officially abbreviated "UTC". Local times around the globe are defined based on UTC by adding to it a time zone bias plus, if locally implemented, a daylight saving time bias. Function utc returns the sum of these two biases: the UTC bias.
Subtracting the UTC bias from a local time produces the equivalent coordinated universal time, a value that will be the same everywhere on Earth (as long as clocks are correct of course). A shortcut notation used to remove the UTC bias from local time is to use the letter Z (as in Zulu) in the output prefix to signal that the value should be shown using UTC time instead. Expression 'z] [12:00] is equivalent to '] [12:00] - utc.
Conversely, a local timestamp can be shown in Zulu time by adding the
value of utc to it. A shortcut notation that has
the same effect is to append suffix Z to the timestamp, just
before the closing bracket, to indicate that the time is stated using the
UTC clock. Timestamp [12:00 Z]
is an equivalent shortcut notation
for [12:00] + utc
.
This reference section has described a number of punctuation characters and letters with special meaning which can be used in the output prefix to dictate the output format or within a timestamp expression to modify its value. They are tabulated here as a summary for reference purposes.
| Mark | Name | Prefix use | Timestamp use |
|---|---|---|---|
| ' | Single quote | Starts a format prefix | |
| [ | Left square bracket | Show the date portion | Start of a timestamp |
| ] | Right square bracket | Show the time portion | End of a timestamp |
| - | Dash | Date token separator | |
| : | Colon | Time token separator | |
| . | Decimal Mark | Millisecond token separator | |
| a or A | Letter A | Show A or P before
a 12-hour clock |
Marks AM time
(before time token) |
| p or P | Letter P | Show A or P before
a 12-hour clock |
Marks PM time
(before time token) |
| s or S | Letter S | Convert to standard time
(add DST bias) |
Using standard time
(no DST bias) |
| t or T | Letter T | Show T before
a 24-hour clock |
Marks 24-hour time
(before time token) |
| z or Z | Letter Z | Convert to UTC
(add UTC bias) |
Using UTC
(no UTC bias) |