timestamp not showing correctly

[sam8599]

Hi All,

I moved my server and SQL database and now getting errors showing timestamp

old server the time stamp

format was 20050316134117

with `time_stamp` timestamp(14) NOT NULL

and the new server has 2006-04-21 15:27:11

with `time_stamp` timestamp NULL default CURRENT_TIMESTAMP,

the script I am using is

        $time_stamp = stripslashes($cats[time_stamp]);
        $year = substr($time_stamp, 0, 4);
        $month = substr($time_stamp, 4, 2);
        $day = substr($time_stamp, 6, 2);
        $hour = substr($time_stamp, 8, 2);
        $min = substr($time_stamp, 10, 2);
        $t = mktime($hour, $min, 0, $month, $day, $year);

        $date = date('M d\t\h\, Y \a\t h\:i a', $t);

instead of showing the result as

April 21st, 2006 at 03:27 pm

all the results show

Dec 04th, 2005 at 09:01 pm

Please suggest what changes I should make in the script

 

[Vragabond]

If you're using MySQL, have you considered formatting your date in the MySQL query? It is very easy and will save you lots of headaches. Check here on how to do it:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#id3174071

 

[sam8599]

Vragabond,

I have just started to learn php/MYsql. Lwearning curve is high.

Appreciate if you can guide me through.

I do pull timestamp in my MYSQL query.

TIA.

regards

 

[sam8599]

Please any help

 

[Vragabond]

What does your query look like? I do believe the the page in question has a pretty good example on how you pull a date out of the column and format it the way you want.

 

[jpadie]

try:

 $time_stamp = stripslashes($cats['time_stamp']);
 $date = date('M d\t\h\, Y \a\t h\:i a', strtotime($time_stamp));
 echo $date;

the problem with the way you were doing it is that you were not allowing for the dashes. for example $month would (i think) be

substr($time_stamp, 5, 2);

you would have found this out if you had followed sleipnir214's FAQ on debuggin in the FAQ section of this site and footprinted your code. eg

$time_stamp = stripslashes($cats[time_stamp]);
        $year = substr($time_stamp, 0, 4);
        $month = substr($time_stamp, 4, 2);
        $day = substr($time_stamp, 6, 2);
        $hour = substr($time_stamp, 8, 2);
        $min = substr($time_stamp, 10, 2);
        $t = mktime($hour, $min, 0, $month, $day, $year);

        $date = date('M d\t\h\, Y \a\t h\:i a', $t);
echo "
timestamp is $time_stamp<br/>
year is $year <br/>
";//etc

 

[sam8599]

Thanks jpadie.... your code worked.

Thanks for helping out.

regards

 

[sam8599]

Hi Jpadie,

I am trying to explore further and learn on the time stamp

What I am trying to learn

1. Picks from Database expire time
2. Declares expire date
3. Compare to current time
4. Declares days left
5. Displays image expire if day passed, ending < 1 day, new >1 day
6. Add days if more than 1 day or display day if <= 1 day

Here what I have done

        $time_exp = strtotime($cats['item_exp']);//expire date from Database
        $dateexpire = $time_exp + 7200;//add server timedifference
        $date_expire = date('m\/d\/Y', $dateexpire);//display date

        $curDate=mktime() + 7200; //current UNIX timestamp.
        $diff= $dateexpire - $curDate ; //date diff in seconds
        $diff = round ($diff / 86400); //date diff in days

         if ($dateexpire > $curDate)
          $img = "<img src='/images/new.gif' border='0'>";//display new image if expire date > 1 day.
         else if ($dateexpire < $curDate)
          $img = "<img src='/images/exp.gif' border='0'>";//display expire image if expire date passed.
         else if ($dateexpire = $curDate)
          $img = "<img src='/images/ending.gif' border='0'>";//display ending image if expire date = 1 day.

echo "$date_expire ($diff)$img";

The problem I am having is that the difference from current date to today's expire date shows -1 and not 0.

Are the if statements correct and also what if statement I should use to display days ("s" gets added for more than 1 days) and it displays day if only 1 day is left.

Please suggest.

TIA.

 

[sam8599]

Hi,

I was able to get the code to work with modifications, I added
(TO_DAYS(NOW()) - TO_DAYS(date_expire)) AS daysToExpiration in my Query to give the difference.

 $display_block = "";

//show categories first
$get_cats =  "SELECT
                item_id,
                sort_id,
                item_title,
                item_desc
                item_code,
                item_exp,
                item_image,
                store_image,
                item_subdesc,
                TO_DAYS(item_exp) - TO_DAYS(CURDATE()) AS item_days_to_expiration
              FROM store_item
              WHERE item_exp LIKE '200%%%%%'
              ORDER BY `item_exp` ASC
              LIMIT $dept_id,$limit";
          
$get_cats_res = mysql_query($get_cats) or die(mysql_error());

if (mysql_num_rows($get_cats_res) < 1) {
   $display_block = "<P><em>Sorry, no categories to browse.</em></p>";
} else {
   while ($cats = mysql_fetch_array($get_cats_res)) {
        $item_id  = $cats['item_id'];
        $sort_id  = $cats['sort_id'];
        $item_title = stripslashes($cats['item_title']);
        $item_desc = stripslashes($cats['item_desc']);
        $item_code = stripslashes($cats['item_code']);
        $item_exp = stripslashes($cats['item_exp']);
        $item_image = stripslashes($cats['item_image']);
        $store_image = stripslashes($cats['store_image']);
        $item_subdesc = stripslashes($cats['item_subdesc']);
        $item_days_to_expiration = $cats['item_days_to_expiration'];

        $display_block .= "<p>$item_title<br>$item_subdesc</p>";
}

What I am now trying is that add if statement (for which I need help)

I want the if statement to show

1. for day passed shows "expired"
2. for "0" as "0 day&nbsp;HOT"
3. for "1" as "1 day&nbsp;ENDING"
4. for ">1" as "2 days&nbsp;NEW"

Basically something like

if ($item_days_to_expiration ==

Please suggest.