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 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”

ALTER TABLE, lägg till fält med SQL

För att lägga in ett nytt fält (kolumn) i en SQL databas så kör man följande fråga:


ALTER TABLE your_tabel ADD your_number_field1 INT(1) NOT NULL, ADD your_text_field2 VARCHAR(145) NULL;

I koden ovanför så lägger vi till två fält, första fältet är ett numeriskt fält som får innehålla en siffra ( INT(1) ) och andra ett textfält som får innehålla 145 tecken ( VARCHAR(145) ). Första fältet får ej vara tomt ( NOT NULL) medan textfältet får vara tomt (NULL).