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
> (YYYY-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
00008876
=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
0000642
=However let's jump forward in time, to tomorrow (hey what's 45 minutes between
friends?):
20020108 less
20020101 equals
00000007
=and:
2002008 less
2002001 equals
0000007
=woohoo! How come they 'work' tomorrow but not today? Because (using the first format)
whilst the last and
second to last digits represent days (hence it 'works' tomorrow), the preceding pair
of digits represent months,
and the procession of days into months is not a decimal progression. (smack your
forehead into the wall
now...but don't do it too often, because no matter how good it feels, it'll feel a
whole lot better when you
stop!)
=this is also the reason that using a Julian date format won't work - they look like
decimal numbers (look like
a duck), you can perform arithmetic on them (walk like a duck), but if your
calculation spans a year-break you
will discover that they are not really decimal numbers (and they bark like a dog).
=The three main date formats are:
1 CCYYMMDD (as mentioned above) because it is the way MySQL does things. You can't use
this for 'real
arithmetic' as we've just discussed, but you can do comparisons,
eg is 'today' > 'yesterday' (when I was young...)
2 dd-mmmmmm-ccyy (or variant) which is the way humans like to read their dates. This
is for appearances, and
once again not for arithmetic/logic.
PS never, never, never (did I say "never") use dd/mm/yy or mm/dd/yy because of the
ambiguities it causes -
particularly between Americans and the rest of the world (and date-guessing functions
- see your manual)
3 UNIX Epoch timestamp which is a count of the number of seconds since midnight 1 Jan
1970 (GMT). This is an
absolutely ugly way to look at dates (and times), but it is really easy to use for
arithmetic and after a while
you don't think it at all odd that without any effort you can recall that there are
86,400 seconds in one day.
=Now that piece of trivia, a pocket protector, and band-aid keeping your spectacle
frames together will make you
a babe-magnet in every bar (well those that serve Heineken anyway).
=Putting aside several HOURS to study the two manuals to get your head around date
functions is time well spent
(yes I know, it takes me a little longer...) - particularly the sheer number of SQL
date functions (the power of
which is all too frequently overlooked). Regarding the PHP functions, I made a page
list, and as I made notes
about each one, I annotated it with a 'purpose' so that I could keep them straight in
my mind. Well, enough of
me and the reasons why girls in bars don't talk to me...
=Regards,
=dn
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
