MySQL convert timestamp to date

Stuck with timestamp when you need a clean date? Just use this bit of code to convert your timestamp into a date.

DATE( FROM_UNIXTIME( create_timestamp ) )

Example of usage (togheter with the SUM function):

SELECT DATE( FROM_UNIXTIME( create_timestamp ) ) AS date, sum( cart_price ) AS price
FROM tbl_shopping_cart
WHERE category_id = 12
GROUP BY DATE( FROM_UNIXTIME( create_timestamp ) )

Explanation:

This query sum the price for each day for a given category in the tabel ”tbl_shopping_cart”.

MySQL SELECT and MULTI DELETE gives different results

If you are running a multi delete query in your MySQL database you will notice that it returns a different result in rows then if you run the same query but with SELECT-statement instead.

Ex.)
SELECT * FROM table1, table2 WHERE table1.id = table2.id AND table2.status = 1
Result: 100 rows
DELETE table1,table2 FROM table1, table2 WHERE table1.id = table2.id AND table2.status = 1
Result: 120 rows

Why is this?

if table1 has 20 rows where each row has 5 related rows in table2, the SELECT-query will give you the mergde result of 20*5 rows (= 100 rows). The delete query in other hand will give you the 20 rows in table1 + the related (20*5 = 100) rows in table2 which in this case is 120 rows.

Simple and obvious? 🙂

MySQL sync-update if not protected

If you have a MySQL query that should sync-update between two tables with the condition that the field your about to update isn´t protected you can use the following query:

REPLACE INTO table1
SELECT * FROM table2 t2
WHERE NOT EXISTS (SELECT * FROM table1 t1 WHERE table1.fieldId = table2.fieldId AND field_protected = 1)

In other words:

REPLACE INTO table1

This line says, ”If an old post exist, delete it and insert this new post (if statements below is fufilled)”.

SELECT * FROM table2 t2

This line says, ”Select all fields from table2, with alias t2”

WHERE NOT EXISTS (SELECT * FROM table1 t1 WHERE table1.fieldId = table2.fieldId AND field_protected = 1)

This line says, ”only select a post from table2 (row above) if the field, field_protected, in the post from table1 hasn´t the value 1”.

or

”only select posts in table2 if there are no hits in the query ”SELECT * FROM table1 t1 WHERE  table1.fieldId = table2.fieldId AND field_protected = 1”