Skip to main content
Inspiring
April 25, 2006
Question

Date Formatting

  • April 25, 2006
  • 15 replies
  • 729 views
I need some help formatting some dates. They're extremely hard for me to
get my head around, for some reason.

I am currently working on this page:
http://demo.mediachurch.com/calendarlist.php (above the hr)

What I am trying to do is capture today's date and compare it with my stored
date in my calendar. The stored date in the calendar is a Serial Date + a
constant. So I need to capture the time, change it to a Serial date, and
then add my constant to compare it to the database.

However, at the moment I'm having a hard time just capturing and formatting
today's date, as you can see. In fact, I'm having a hard time just getting
my formatting script to work as it keep spitting out the same date.
*However* the frustrating part is that my script works to pull out and
format the dates from the calendar (see below the <hr> .

Here's what I have:

MY TWO RECORDSETS (I can duplicate if you need them)

MY FUNCTIONS AND VARIABLE DEFINITIONS

//VARIABLES
//Get Today's Date
$priivaDateToday = time();
$priivaTestDate = $row_RECORDSETSTUFF


define('EPOCHINEXCEL', 25568); //01-01-1970 minus 1 Defines the serial
date of 1970, which should ensure the script works on windows machines

//This changes the date from MS (days since 1900) to Unix (seconds since
1970)
function priivaExcel2Unix($n) {
return mktime(0, 0, 0, 1, $n - EPOCHINEXCEL, 1970);
}

//This changes the date from Unix to Excel
function priivaUnix2Excel ($n) {
return mktime (0, 0, 0, 1, $n + EPOCHINEXCEL, 1970);
}


THE CODE TO DISPLAY IT ALL

echo "<p> Today's Date: $priivaDateToday </p>";
echo "<p> Today's Date Unix 2 Excel: ";
echo date('l, M jS', priivaUnix2Excel($priivaDateToday));
echo "</p>";
$priivaDateToday = time();
echo "<p> Today's Date $priivaDateToday </p>";
echo "<p> Today's Date Excel 2 Unix: ";
echo date('l, M jS', priivaExcel2Unix($priivaDateToday));
echo "</p>";

echo "<p> First Recordset: $priivaTestDate </p>";
echo "<p> First recordset, formatted: ";
echo date('l, M jS', priivaExcel2Unix($priivaTestDate));
echo "<br />wtf?</p>";
echo "<hr>";


Now, just to clarify a little bit, this is what's on the rest of the page
(ie, the stuff below the HR that is formatting correctly). I have left out a
few functions and variable definitions as it would archive my database
structure and leave obvious security holes.:

//here we write out the head statement if the date is different
if ($priivaDateSame == 0){
?>
<dt><?php echo date('l, M jS', priivaExcel2Unix($priivaDateAdjusted)); ?>
/ <?php echo $priivaEventDate ?></dt>
<dd><?php echo $priivaEventTitle; ?></dd>

<?php
//If it is the same date, don't repeat the header
}
else if ($priivaDateSame == 1) {
?>
<dd><?php echo $priivaEventTitle; ?></dd>
<?php
//this is the completion of the If statement to check dates
}
//This is a completion of the While loop going through the recordsets
}
while ($RECORDSETSTUFF = mysql_fetch_assoc($RECORDSETSTUFF));
echo "</dl>";
//clears the recordset so it can be re-used.
mysql_free_result($RECORDSETSTUFF);

mysql_free_result($rsDateTest);
?>



--

TIA,

Jon Parkhurst


This topic has been closed for replies.

15 replies

Inspiring
April 25, 2006
.oO(crash)

>> Wouldn't it be easier to let the database return a properly formatted
>> date already instead of fiddling around with this serial nonsense in
>> your script?
>
>Yes! It sure would. Are you subtracting that constant in the @ statements
>below? It looks like you are with the @adjust.

The @adjust is just a variable I set before sending the query. I could
also have typed the constant directly in the query. This constant is
only used to adjust your serial dates to the range that FROM_DAYS()
uses.

>I'm sorry this is taking me so long to catch on to. I don't know SQL or PHP
>very well at all and 1/2 of my time is spent tryign to understand the
>correct notation that I need to put somethign into, and the other 1/2 is
>spent looking up code fragments to see if they do what I think they do.



>However, your method looks more efficient if it does what I think it does
>and might be much better.

Hopefully it also does what _I_ think it does ... :-D

But according to some little tests it works. A full example query
without any variables for the serial date '2454311':

SELECT DATE_FORMAT(FROM_DAYS(2454311-1721060), '%W, %b %D') AS date;

The following would return a Unix timestamp for the same date:

SELECT UNIX_TIMESTAMP(FROM_DAYS(2454311-1721060)) AS date;

Micha
Inspiring
April 25, 2006
I'm re-reading this and your original post.

My problem while working on this has been that I get to seeing things in my
way, and it's very hard for me to look virtually at another approach - which
I believe is why I didn't see the value in your original post. Now that I
have a better understanding of what's going on, I can understand it a bit
better.

I'm *so bad* with time. I even missed my birthday last year.


Inspiring
April 25, 2006
Micha-

Thanks for responding - please read below. I'm also looking at your code
and looking it up to understand it, so I might post back with my
re-interpretations.

> Wouldn't it be easier to let the database return a properly formatted
> date already instead of fiddling around with this serial nonsense in
> your script?

Yes! It sure would. Are you subtracting that constant in the @ statements
below? It looks like you are with the @adjust.

I'm sorry this is taking me so long to catch on to. I don't know SQL or PHP
very well at all and 1/2 of my time is spent tryign to understand the
correct notation that I need to put somethign into, and the other 1/2 is
spent looking up code fragments to see if they do what I think they do.

BTW - I got it figured out, I think. I was pulling a typical Jon and just
not reading hard enough - I found my error, and as usual it was silly.
However, your method looks more efficient if it does what I think it does
and might be much better.



Inspiring
April 25, 2006
.oO(crash)

>What I am trying to do is capture today's date and compare it with my stored
>date in my calendar. The stored date in the calendar is a Serial Date + a
>constant. So I need to capture the time, change it to a Serial date, and
>then add my constant to compare it to the database.

Wouldn't it be easier to let the database return a properly formatted
date already instead of fiddling around with this serial nonsense in
your script?

I already posted something that might help, which involves calling the
FROM_DAYS() function in MySQL (I just used a wrong constant, so the
results were off by one year). It can be used to turn the serial day
count into a proper yyyy-mm-dd date. This can then be used for all kinds
of date calculations in the DB itself or simply returned as a Unix
timestamp to your script.

Dealing with "raw" data like a Unix timestamp or an ISO-date is much
easier than with some proprietary format, which always requires some
tweaking here and there.

Some tests with your serial dates:

mysql> SET @adjust := 1721060;
mysql> SET @format := '%W, %b %D';

mysql> SELECT DATE_FORMAT(FROM_DAYS(2453737-@adjust), @format) AS date;
+-----------------+
| date |
+-----------------+
| Sunday, Jan 1st |
+-----------------+

mysql> SELECT DATE_FORMAT(FROM_DAYS(2453833-@adjust), @format) AS date;
+-----------------+
| date |
+-----------------+
| Friday, Apr 7th |
+-----------------+

mysql> SELECT DATE_FORMAT(FROM_DAYS(2454311-@adjust), @format) AS date;
+------------------+
| date |
+------------------+
| Sunday, Jul 29th |
+------------------+

Micha
Inspiring
April 25, 2006
i think i might have got it figured out, thoughts on optimizing code would
be welcome.