Time-Related Expression Language Techniques

TABLE OF CONTENTS

• Time-Related Expression Language Techniques
• Facts About Timestamps and Dates
  • Timestamps vs Dates
  • The Zero Date:  1/1/1970
• Comparing Time - AM vs PM or Time A vs Time B
  • Determining AM vs PM
  • Comparing Time Numerically
• Formatting Dates and Timestamps

Facts About Timestamps and Dates

Timestamps vs Dates

"Date" and "Timestamp" are two built-in data types.  Both can hold a date and a time - "Date" will often have an unset "time" and "Timestamp" will often include a "zero date" (see below).  A "zero date" prints as 1/1/1970.

Date and Timestamp both measure 'time' from January 1, 1970.   Consequently both will need a revision in the future so they do not 'run out' (a la The Year 2000 Problem).  The current version of Timestamp runs out on January 19, 2038 at 03:14:07 Greenwich Mean Time (Universal Time) or, in EST: 10:14:07pm January 18, 2038.  (Not to worry: there will be a conversion before then).  Similarly, Dates will require updating at some point.

Timestamps require conversion to Dates to work with certain functions.  The function rw:toDate is provided:  rw:toDate(yourTimeStamp).

The Zero Date:  1/1/1970

The "zero date" is 1/1/1970  (January 1st, 1970) and the "zero time" is 00:00.   When a Date or a Timestamp is not set, it appears as 1/1/1970 00:00.   That is, if you should print a date and you get "1/1/1970" it means the Date is not set - it is essentially "empty".  

Comparing Time - AM vs PM or Time A vs Time B

Determining AM vs PM

To determine if something occurred before or after 12:00pm Noon, you can simply determine whether it is an "AM" vs "PM".   The rw:formatDate function is useful for this.

rw:formatDate(row.yourTimetamp,'a')  returns either 'AM' if the Timestamp has a 'pre-noon' time and 'PM' is the Timestamp has an afternoon time.

rw:formatDate(row.yourTimestamp,'a') == 'PM'   --> tests whether the time is afternoon

Comparing Time Numerically

If you need to do something more nuanced than AM vs PM, you can calculate time numerically on a 24 hour clock.   Doing the calculation  Hours *100 + minutes gives a numeric value in the range 0 to 2359 (using a 24 hour range of hours 0 to 23).

The calculation is done like this: (Note that 'HH' gives hours on the 24 hour clock, 'hh' gives hours 0 to 12):

rw:toInteger(rw:formatDate(row.YourTimeStamp,'HH')) * 100 +  rw:toInteger(rw:formatDate(row.YourTimeStamp,'mm'))    

The calculation above runs from 00:00 to 23:59  (or 0 to 2359).  Converting times in this way allows you to compare the times numerically. 

Formatting Dates and Timestamps

As illustrated above the formatDate function is used to format both Dates and Timestamps.

Some of the formats available include:

• M - Capital M gives "month" information.  You can use:
  • M - 1 to 12
  • MM - 01 to 12
  • MMM -  Jan to Dec
  • MMMM - January to December
• d - Lower case D gives "day":
  • d - 1 to 31
  • dd - 01 to 31
• y - Lower case y gives 'year':
  • yy - 00 to 99
  • yyyy - 2000 and up.
• HH - Upper case H gives time on the 24 hour clock  (hour in 0 to 23)
  • HH - 0 to 23
• hh - Lower case H gives time from 1 to 12
• m - Minute in hour
  • m - 0 to 59
  • mm - 00 to 59)
• a - lower case a gives A or P  (AM, PM)
• k - Hour in day (1 to 24)
• K - Hour in am/pm (0-11)

Some other, less frequently used formats include:

• G - Era (AD, BC)
• L: Month in year (standalone form) (similar to M but for standalone month names)
• w: Week in year (e.g., 27)
• W: Week in month (e.g., 2)
• D: Day in year (e.g., 189)
• F: Day of week in month (e.g., 2nd Tuesday of the month)
• E: Day name in week (e.g., E for Tue, EEEE for Tuesday)
• u: Day number of week (1 = Monday, ..., 7 = Sunday)

• s: Second in minute (e.g., ss for 55)

• S: Millisecond (e.g., SSS for 978)

• z: Time zone (general time zone, e.g., Pacific Standard Time; PST; GMT-08:00)