• Insert the row, but if the record already exists then update a field.

INSERT INTO table (x,y,z,t) VALUES (1,2,3,10) ON DUPLICATE KEY UPDATE z=0.3, t=10

 

  • multiple time queries

I had some items from Facebook walls. I had two dates, one on item creation(user posts the item), one on item retrieval(I read the item from Facebook). I wanted to show items that are posted or retrieved today.

SELECT link,created_time FROM homeWallItems WHERE
DATE_SUB(CURDATE(),INTERVAL 1 DAY)<= created_time
OR
DATE_SUB(CURDATE(),INTERVAL 1 DAY)<= update_time
group by update_time LIMIT 0,30
  • Add days to a date
select * from friendship_links WHERE referrer = {$user} and start>DATE_ADD('{$first_date}',INTERVAL 10 DAY) order by start asc
  • Find difference between two mysql timestamps ( Subtract mysql timestamps ). Below, this shows how many days each user has been active. You can write MINUTE, MONTH or YEAR instead of DAY to get relevant results.
SELECT user, TIMESTAMPDIFF(DAY, min(activity_date), max(activity_date)) FROM `data_table` WHERE 1 group by user
  • Export chosen mysql columns to a file
SELECT node1, node2
  INTO OUTFILE 'C:/Users/co/Desktop/Dropbox/results.csv'
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
FROM edges;
Advertisements