Gidday Justin,
For us guys that don't get dates very often, the subject is one of intense
fascination! I answered a bunch of
these questions a couple of weeks back, and reproduce that discussion below. Also some
comments/responses to you
interspersed:-
> I'm looking to normalise the way in which I work with dates and times,
> hopefully saving myself some time and effort along the way.
>
> Currently, for, say, a news post, i'm using a MySQL DATE column
> (-MM-DD), but since this isn't all that good (visually) for use on a
> site, I find myself converting it back out to something like DD-MM-YY or
> DD-MM for use on the site. Not exactly hard, but it's gotta be done
> every time.
=to ease the effort of repeated activities employ/implement a function (as you say,
saves time). Fortunately PHP
and SQL/MySQL provide us with plenty, out of the box.
> Now I find myself wanting to sort things by date (where the ID might be
> in the wrong order... so 2001-12-25 is listed before 2001-12-26, etc),
> and more importantly, comparing two dates (3 days away, 3 days ago, 3
> hours ago, etc etc).
=the reason many people find ISO-format dates confusing is simply that they've grown
up with something else
(presumably in your case dd/mm/yy). If you sit down and think about the sequence of
data, running from the
largest unit to the smallest, consistently, you will see that it makes good sense.
These have been adopted as
local/national standards in various places, eg the EU, but we still don't see them too
much - guess we're still
smarting from KM vs miles and KG vs pounds-weight (I'm still paying off the bill for
that 1KM of sausages I
ordered...)
=the reason it makes good sense in a computer is because it will fulfil basic
functions in both use of a date:
(1) to document some date/time, and (2) to be used in basic computations. The best
example I can think of is a
logging function, eg OpSys tools and Apache. Take a look at the native output any of
these and you'll see ISO
dates left, right, and center. Other date formats will fulfill one or other of these
functions, but will not
compromise between both so well.
=if you want to sort an ISO date column, go ahead and do it. ISO dates can be
compared, just as they are:
2001-12-25 < 2001-12-26 = true
thus Christmas Day came before Boxing Day, last year (and most other years...)
=This also applies to ISO dates expressed as an integer
20011225 < 20011226 = true
=If you want to perform calculations on ISO-dates then check out the date functions
section in the MySQL manual
for DATE_ADD, DATE_DIFF, etc. They will achieve your comparisons, adjustments by
period of time (days), etc.
=In the same way, PHP has a rich set of date calculation functions, also well covered
in their manual. However
these are complementary and deal in the PHP prefered date formats.
=Sadly, there is no substitute for hitting the books (see advice on this below).
> Would I be better off using a unix timestamp for everything, then using
> it to:
>
> - convert to different formats
> - compare
> - sort in date order
> - etc etc
>
> Also, I've noticed there is a timestamp column type in MySQL... is there
> a way to cut down on PHP code by using MySQl alone to enter the
> timestamp (or date) for me?
=see comments below about selecting the date format to suit the usage/purpose intended
(if you don't want to
spend all day converting back and forth - spending time, re-making time?).
=There is no ONE right answer - that's why we've been given choices. BEWARE of the
word "timestamp" in that a
UNIX timestamp is not the same thing as a MySQL timestamp!
> Any other suggestions on a sensible method of implementing dates & times
> accross many sites and many bits of code?
=here they come... one of your respondents talked about doing subtraction on ISO
dates. This will not work - the
discussion (below) started off with this question "why doesn't it work?":-
RE: [PHP] counting with dates (help!)
> $today = date("Ymd", mktime(0,0,0, date(m),date(d),date(Y)));
> $last_week = date("Ymd", mktime(0,0,0, date(m),date(d)-7,date(Y)));
> echo ($today - $last_week);
> The result is a number like 8876 (20020107-20011231 = 8876)
> But in date thinking it should be 7!
No, that's the difference in time represented by the number of seconds.
You still need to work with it a little more.
8876 / 60 = number of hours /* 60 = number of seconds in an hour */
8876 / 60 / 24 = number of days. /* 24 = number of hours in a day */
=I'm sorry but neither the above, nor the suggestion of Julian dates was correct (in
all cases). The two numbers
($today and $last_week) generated in the PHP code above are in CCYYMMDD format (as
used by MySQL to store dates,
BTW).
=So you are correct (Sander):
20020107 less
20011231 equals
8876
=but this number is meaningless. If the formulae proposed above are applied, the
answer is not 7 days.
=Similarly (Julian dates = CCYYDDD format)
2002007 less
2001365 equals
642
