MySQL Snippets

Basic INSERT example

INSERT INTO time_data (
    the_date,
    time_in,
    time_out,
    duration,
    entry,
    painting_title,
    painting_status
) VALUES (
    'YYYY-MM-DD',
    'HH:MM:SS',
    'HH:MM:SS',
    'HH:MM:SS'',
    2,
    'Old Hall Closet',
    'in progress'
);

Add a completed row of data

INSERT INTO time_data (
    the_date,
    time_in,
    time_out,
    duration,
    entry,
    painting_title,
    painting_status
)
VALUES (
    'YYYY-MM-DD',
    'HH:MM:SS',
    'HH:MM:SS',
    'HH:MM:SS',
    2,
    'Old Hall Closet',
    'in progress'
);

Update CURRENT_TIMESTAMP

UPDATE your_table_name
SET created_at = TIMESTAMP('YYYY-MM-DD HH:MM:SS')
WHERE created_at = 'YYYY-MM-DD HH:MM:SS'; -- this is the one that gets replaced

Total Duration Between ID’s

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS Total_Duration
FROM time_data
WHERE id BETWEEN 131 AND 243;

Adding New Columns

ALTER TABLE time_data
ADD COLUMN image VARCHAR(255),
ADD COLUMN painting_title VARCHAR(255),
ADD COLUMN painting_status VARCHAR(255);

Transfer Columns From One Table to Another

UPDATE time_data td
INNER JOIN progress p ON td.uuid = p.uuid AND td.timestamp_for_DB = p.timestamp_for_DB
SET
td.image = p.image,
td.painting_title = p.painting_title,
td.painting_status = p.painting_status;