CODEWITHSUNDEEP
mysql
Yevgeniy Bagackiy
Yevgeniy Bagackiy

Reputation: 974

Add timestamp column to the table created from query

I want to create table from mysql query but add a new column with timestamp where I will capture date of creation.

Here is my query:

CREATE TABLE archive
  AS ( SELECT a.* FROM
        (
            (
              SELECT p.projectNo, usr.staff_id, usr.longname, usr.username, p.title, CONCAT(upr.process, " (", upr.role, ")" ) AS process
              FROM project p
                INNER JOIN user_project upr ON p.projectNo = upr.projectNo
                    INNER JOIN user usr ON upr.username = usr.username
            )
        ) AS a
        ORDER BY a.projectNo);

How can I add timestamp?

Upvotes: 1

Views: 65

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521514

You can selecting NOW() as an extra column for the creation timestamp, e.g.

CREATE TABLE archive AS
(
    SELECT a.*
    FROM
    (
        (
            SELECT p.projectNo,
                   usr.staff_id,
                   usr.longname,
                   usr.username,
                   p.title,
                   CONCAT(upr.process, " (", upr.role, ")" ) AS process,
                   NOW() AS creation_time     -- added column
            FROM project p
            INNER JOIN user_project upr
                ON p.projectNo = upr.projectNo
            INNER JOIN user usr
                ON upr.username = usr.username
        )
    ) AS a
    ORDER BY a.projectNo
);

Upvotes: 2

Related Questions