Dates/Times
From FMYiRCWiki
Freemed-YiRC Date/Time Functions
Contents |
Summary
This explains how to use the Freemed-YiRC date and time functions.
Date Functions
fy_putdate()
- Usage: fy_putdate($fydb_type, MONTH, DAY, YEAR);
- Takes the month, day and year and returns the date in the database date format.
- Returns Example: 2009/01/07
fy_getdate()
- Usage: fy_getdate($fydb_type, DATE);
- Takes a date (in database format) and returns the date in MM/DD/YYYY
- Returns Example: 01/07/2009
fy_getlocdate()
- Usage: fy_getlocdate($conn, DATE, $fydb_type, $fydb_name);
- Takes a date (in database format) and returns the date in the locale format (as specified in the Freemed-YiRC settings).
- Returns Example: 01/07/2009
Time Functions
fy_puttime()
- Usage: fy_puttime ($fydb_type, HOUR, MINUTE, AM/PM)
- HOUR = 0-12 (non military time)
- MINUTE = 0-59
- AM/PM = AM or PM
- This function does not deal in military time.
- Returns Example: 23:05:00
fy_gettime()
- Usage: fy_gettime(TIME)
- TIME = HH:MM(:SS)
- Takes military time and returns 12 hour AM/PM time.
- Returns Example: 12:05PM
fy_getloctime()
- Usage: fy_getloctime($conn, TIME, $fydb_type, $fydb_name);
- Takes a time (in database/military format) and returns the time in the locale format (as specified in the Freemed-YiRC settings).
- Returns Example: 12:05PM
Example Date Code
$gr_month = date("m", mktime());
$gr_date = date("d", mktime());
$gr_year = date("Y", mktime());
$gr_date=fy_putdate($fydb_type, $gr_month , $gr_day, $gr_year);
$gr_cdate = fy_getlocdate($conn, $gr_date, $fydb_type, $fydb_name);
Return locale date from dates stored in database
$fy_date = fy_getlocdate($conn, fy2_getfield($result_set, ''ROW'', ''FIELD''), $fydb_type, $fydb_name);
Date Range Logic In SQL
When dealing with date ranges in Freemed-YiRC and SQL where you are looking for records which may occur between date X and date Y, but the records themselves may not simply have a date stamp and also have a start date and end date, things can get somewhat complicated. The following information should help.
Example
As an example, let's say you want to see how many staff contacts were employed between 01/01/2008 - 12/31/2008. This is not a simple search as staff are employed over a range of time, not just a single date. In the Freemed-YiRC table fy_con_stf we have two relevant fields here, doh (Date of Hire) and dot (Date of Termination). (Likewise, residents have doe (Date of Entry) and dot (Date of Termination)). So now you have to sets of date ranges... your original target date range (01/01/2008 - 12/31/2008) and now every record has a date range.
Also complicating this situation is the fact that staff may still be working, they may not have a date of termination. We have to take this into account.
I have found that the following chart helps when dealing with comparing date ranges against each other:
In this diagram, Target Start Date and Target End Date refer to the date range you are looking for... in this case:
- Target Start Date = 01/01/2008
- Target End Date = 12/31/2008
The horizontal lines represent DB records.
- The green dots are start dates. Back to our example, this would be the Date of Hire (doh) from the fy_con_stf table.
- The red dots are end dates. In our example, this would be the Date of Termination (dot).
We only want to show records that occur during our date range. In the diagram this is displayed by some of the top three horizontal lines having portions between the Target Start Date and Target End Date vertical lines. The last two lines fall completely either before or after our target date range. Those are the records we want to exclude from our search.
Note the pattern for the lines that we do want, the top three.
- The green dot (record start date) always shows before the Target End Date
- The red dot (record end date) always shows after the Target Start Date
This will form the basis for our logic. From this, we can form an SQL statement:
SELECT * FROM fy_con_stf WHERE doh<='2008/12/31' AND dot>='2008/01/01';
However, this does not take into account staff who may still be active and have dot set as NULL. Hence, we modify our SQL to:
SELECT * FROM fy_con_stf WHERE doh<='2008/12/31' AND (dot>='2008/01/01' OR dot is null);
Notes
Sometimes, for a number of reasons, it's easier to set the end date to 01/01/2009 in our previous example. When that's the case and you DO NOT wish to include the day 01/01/2009 itself in the date range then the equal sign (=) relating to that date needs to be removed as to not include that date itself... so as to make it less than instead of less than or equal to:
SELECT * FROM fy_con_stf WHERE doh<'2009/01/01' AND (dot>='2008/01/01' OR dot is null);
