Date and Time Handling in Common Languages
In this article, we describe how to handle date and time in a few common programming languages with a simple illustrative example. In fact, date and time handling may be more complex and involved than you'd thought. The best strategy is probably not to invest time on writing your own functions. Instead you should use existing libraries as much as possible which can save you a lot of headaches in the long run.
The problem is: Given a time string such as '2009-09-20 09:10:15,' return the first day of the week that the time string is in. Here we assume the first day of a week is Monday. So '2009-09-14' should be returned.
MySQL
MySQL is full-fledged open source DBMS. It provides many useful functions and following we describe how the above problem can be solved in one SQL statement with the help of MySQL built-in functions.
For easy experimentation, we first create a simple table that just stores a week's timestamps.
create table ts (
ts timestamp,
primary key (ts)
);
insert into ts values('2009-09-20 09:10:15');
insert into ts values('2009-09-21 09:10:15');
insert into ts values('2009-09-22 09:10:15');
insert into ts values('2009-09-23 09:10:15');
insert into ts values('2009-09-24 09:10:15');
insert into ts values('2009-09-25 09:10:15');
insert into ts values('2009-09-26 09:10:15');
You can download the sample date and time MySQL file and experiment with it.
Next we should consult the MySQL manual for its date and time functions.
The dayofweek function helps to serve our purpose:
It returns 1 for
Sunday, 2 for Monday and etc based on the ODBC standard. Then
we should subtract the following number of days to get
the Monday of the same week that the given date is in.
| Day | Day of week (dow) | Days to subtract from the day |
| Sunday | 1 | 6 |
| Monday | 2 | 0 |
| Tuesday | 3 | 1 |
| Wednesday | 4 | 2 |
| Thursday | 5 | 3 |
| Friday | 6 | 4 |
| Saturday | 7 | 5 |
Now we need to find a function that accepts the value of the second
column and returns the value of the third column. With a bit of thinking,
we can find out that the mod function can do the trick
and the expression is mod(dayofweek(ts)+5, 7).
Now we can use the date_sub to do the subtraction and
then use the date function to retrieve the resulting
dates. So the complete SQL statement is:
select date(date_sub(ts, interval mod(dayofweek(ts)+5, 7) day)) from ts;
SQLite
SQLite is a simple and lightweight DBMS that supports transactions. It doesn't boast of having many features. However, its date and time functions suffice for our purpose.
Please note that SQLite provides only a few data types and its handling
of types can be called
Duck typing.
For convenience, we use the text type to store the timestamp
string.
You can download
the sample date and time SQLite file
which is almost identical to the sample MySQL file we discussed earlier.
The format string %s used by the strftime function
returns the day of week with Sunday equals to 0. Please note here the difference
from the MySQL dayofweek function. We can get the days to subtract
with the following expression: strftime('%w', ts) + 6) % 7.
Then we can get the Unix timestamp for the first day of the week with the
following expression:
strftime('%s', ts) - (strftime('%w', ts) + 6) % 7 * 86400
The above expression returns the Unix timestamp and we can use the
date function with the 'unixepoch' modifier
to get the date which is the first day of the week that the given date
is in. So the complete SQL statement is:
select date(strftime('%s', ts) - (strftime('%w', ts) + 6)%7 * 86400, 'unixepoch') from ts;
Perl
The Perl motto says it well: There is more than one way to do it. Given that there are so many Perl modules that handle date and time on CPAN, following we just show one example.
First we can use the
DateTme
module to handle all the date arithmetic. For example, it has the
day_of_week function that returns 1 for Monday, 7 for Sunday and etc.
It also has the add function to allow you to add to or subtract from
the DateTime object arbitrary number of days. Interestingly, the
DateTime module doesn't handle any date parsing. You have to use those
DateTime::Format::* modules from CPAN to parse the date.
To parse a date/time string like '2009-09-20 09:10:15,' we can use the
DateTime::Format::SQLite module. It returns a
DateTime object after parsing the string.
$ts = '2009-09-20 09:10:15';
$dt = DateTime::Format::SQLite->parse_datetime($ts);
Then you can combine the day_of_week and the add
functions to get the first day of the week that the date is in:
$dow = $dt->day_of_week;
$dt->add(days => (1 - $dow));
Then you can use the ymd function to print the day in
the format of YYYY-MM-DD:
print $dt->ymd('-');
You can download the complete Perl script that returns the first day of the week that the given date is in and test it yourself.
PHP
We use the
Date package from the
PEAR - PHP Extension and Application Repository
so we don't have to roll our own data arithmetic functions. Similar to SQLite,
the following code shows how to get the first day of the week for the given
$datestr.
$date = new Date($datestr);
$dow = $date->getDayOfWeek();
$date->addDays(-($dow + 6) % 7);
return $date->format("%Y-%m-%d");
You can download the complete PHP script that returns the first day of the week that the given date is in and test it yourself.
Please note that you need to install the Date package 1.5.0 and
above to get the addDays function.