[RESOLVED] Grouping unix dates

DeathfireD

I've been saving articles to a database but the date formats used by other people are not always the same format. So I use

  strtotime("first day", strtotime($date));

to convert them all into unix date/time stamps.

Now heres the problem. I have a tag cloud that groups the dates up...but since its in the unix date/time stamp when I group it doesn't quite work. If a date in the database has the hour, it will not be grouped correctly even if it has the same day. For example date july 23, 2007 and july 23, 2007 12:10 will not group due to the time being added but dates july 24, 2007 12:10 and july 24, 2007 12:10 will.

Is there anyway to get rid of the hour part when grouping and just keep the month, day, year?

heres how I group the dates to make the tag cloud.

$query = "SELECT date AS tag, COUNT(id) AS quantity
  FROM headlines
  GROUP BY date
  ORDER BY date DESC";

and loop through the results

while ($row = mysql_fetch_array($result)) {

   $tags[$row['tag']] = $row['quantity'];
}

troybtj

$query = "SELECT date, DATE_FORMAT(date, %Y-$m-%d) as day, COUNT(id) AS quantity
  FROM headlines
  GROUP BY day
  ORDER BY day DESC";

You wouldn't have the ability to group by hours, but this would strip the timestamp and leave the raw date.

DeathfireD

troybtj wrote:
$query = "SELECT date, DATE_FORMAT(date, %Y-$m-%d) as day, COUNT(id) AS quantity
  FROM headlines
  GROUP BY day
  ORDER BY day DESC"; 
You wouldn't have the ability to group by hours, but this would strip the timestamp and leave the raw date.

I not looking for the hours. I just want to group by day so all the news that was added on monday 21, 2007 show up as a tag.

I tried your code

$query = "SELECT date, DATE_FORMAT(date, %Y-$m-%d) as day, COUNT(id) AS quantity
  FROM headlines
  GROUP BY day
  ORDER BY day DESC";

$result = mysql_query($query);


// here we loop through the results and put them into a simple array:
// $tag['thing1'] = 12;
// $tag['thing2'] = 25;
// etc. so we can use all the nifty array functions
// to calculate the font-size of each tag
//while ($row = mysql_fetch_array($result)) {
while ($row = mysql_fetch_array($result)) {

   $tags[$row['tag']] = $row['quantity'];
}

and got errors with the start of the while loop.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\site\datetag.php on line 31

I also tried changing your code to this and a few other ways but none worked and I kept getting the same error with the loop.

$query = "SELECT date, DATE_FORMAT(date, %Y-$m-%d) as tag, COUNT(id) AS quantity
  FROM headlines
  GROUP BY day
  ORDER BY day DESC";

troybtj

I forgot the quotes around the format string. Should be: "%Y-%m-%d"

If the column is defined as a "DATE" datatype in the database

select DATE_FORMAT(date, "%Y-%m-%d") as day from CN_user group by day order by day desc;

DeathfireD

troybtj wrote:
I forgot the quotes around the format string. Should be: "%Y-%m-%d"

If the column is defined as a "DATE" datatype in the database
select DATE_FORMAT(date, "%Y-%m-%d") as day from CN_user group by day order by day desc;

I have it defined as text in the db. I probably should have set it to date before hand. Is their a way to still do it without having it defined as date?

For some reason when I change it from text to date it changes all the unix dates to 0000-00-00 on my test server. I understand its the format for year, month, day but I have no clue why it changed them all to 00's.... :S

I'm saving the dates using this

strtotime("first day", strtotime($date));

troybtj

dates are best stored in unixtime format (seconds since 1970).

If you have the date/time in a string, The conversion would be a complex query to select * from your current table and insert the date in the correct format in a new table.

There may be other ways, there are some DB Wizzards around here that might have a better idea.

Otherwise, you could try the SUBSTR(field, 1,10) select to get the first 10 digits out of your date field, but it would be slower than an optimized table. If resource demands are low, this could be a workaround.

DeathfireD

I can fix the dates in the database later. I first want to know how I should be saving the dates into database since apparently using this to save the date to unix time strtotime("first day", strtotime($date)); is not working went the field is set to date.

🙁

DeathfireD

I figured it out.

a friend suggested changing my save part to this since my database field is now set to DATE.

  $ubdate = addslashes(strtotime("first day", strtotime($rss->items[$i]['pubdate'])));
  $pubdate = date("Y-m-d", $ubdate);

It worked, all data that was saved into the database was saved in the year, month, day format. I went back and used my original tag cloud code and everything ended up working out find.

Thanks for the helpful start.