Mysql and PHP Timestamp

[jucedupp]

Hi there all

 

I have an interesting problem.  I am trying to write a custom query for a weekly sales report. 

 

The problem is that I have to group weekly sales by week number.  Now, the date comes from a virtuemart table, and it is not a Mysql or Unix timestamp, but a standard PHP timestamp.  So  I have timestamps that look like this: 1218798821

 

This obviously includes hour, minute second so, I cannot simply group by my column, (which is named cdate, by the way) as each record is unique, due to the fact that the timestamp includes seconds. 

 

Now, the question is: how can I, using Mysql/PHP group these dates by week? 

 

I am thinking in the direction of doing something like using a PHP time or date function to give me a timestamp without hhmmss and then creating a string of variables each for the beginning and end of a week, but even if I could write something like that, it would still mean looping the data through the php function (or whatever it may be).  This could make the query time very long.

 

The query is as follows:

 

(I am not including the structure etc because there are so many joins etc.)

 

SELECT * FROM
za_vm_orders
LEFT OUTER JOIN za_vm_order_item ON (za_vm_orders.order_id=za_vm_order_item.order_id)
INNER JOIN za_vm_product ON (za_vm_order_item.product_id=za_vm_product.product_id)
INNER JOIN za_vm_user_info ON (za_vm_order_item.user_info_id=za_vm_user_info.user_info_id)
INNER JOIN za_vm_product_category_xref ON (za_vm_product.product_id=za_vm_product_category_xref.product_id)
INNER JOIN za_cam_cellar ON (za_vm_product_category_xref.category_id=za_cam_cellar.category_id)
INNER JOIN za_comprofiler ON (za_cam_cellar.cellar_name=za_comprofiler.cb_wineryid)
INNER JOIN za_vm_country ON (za_vm_country.country_3_code=za_vm_user_info.country)
WHERE
  (za_comprofiler.user_id = 99) 

 

This gives me a long table with many fields (which I will trim down later).

 

The field that contains the date is  za_vm_order_item.cdate and this contains the PHP timestamp.

 

As you notice I am a newbie, so any guru help will be greatly appreciated and I am sure it will boost your karma so much that your children will be rich and you won't have to work ever again. 

 

 

Thanks

 

Jacques

 

[PFMaBiSmAd]

The unix timestamp must undergo a conversion for it to be used in almost every instance.

 

Your best solution would be to store the information as a DATETIME data type instead. You can then directly use any of the mysql date/time functions to extract, match, select, compare... the data any way you want.

 

For your existing situation, the second best solution would be to use the mysql FROM_UNIXTIME() function in the query to get a DATETIME type and then use the other mysql date/time functions to extract, match, select, compare... the data any way you want. Because this adds a slow conversion to the data in the query, it is slower than if the columns were already a DATETIME type.

[fenway]

And once they are proper DATETIME fields, you can use WEEK() to extract a week number for grouping.