MySQL Auto-Store Datetime for Each Row

MySQL auto-store datetime for each row

You can use DEFAULT constraints to set the timestamp:

ALTER TABLE
 MODIFY dt_created datetime DEFAULT CURRENT_TIMESTAMP

ALTER TABLE
 MODIFY dt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Then you wouldn't have to specify NOW() in your INSERT/UPDATE statements.

Reference: TIMESTAMP properties

Auto update DATETIME column on insert with SQL?

It seems the issue was that the column needed to be a TIMESTAMP not DATETIME upon changing it, I was able to successfully add the CURRENT_TIMESTAMP argument.

Automated date column in MySQL when row is added

Use this MySQL query to make default value as CURRENT_TIMESTAMP for the required field:

ALTER TABLE `table` CHANGE `orderDate` `orderDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;

This is if you're running MySQL <5.6.5. Above, you can have your field to be of DATETIME and therefore run the following query:

ALTER TABLE `table` CHANGE `orderDate` `orderDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

MySQL date column auto fill with current date

MySQL unfortunately doesn't allow specifying values other than constants as the default for columns other than TIMESTAMPs.

This is a feature available in MySQL versions 8.0+, but for older versions the only solution for a database defined default would be to use a trigger.

MySQL column that auto fills TIME when data is inserted in row

Reuben. You may try on the following codes.

DELIMITER ;;
CREATE TRIGGER device
BEFORE INSERT ON device
FOR EACH ROW
BEGIN
    IF new.date IS NULL THEN
        SET NEW.date = CURDATE();
        SET NEW.time = CURTIME();
    END IF;
END
;;

You can refer further on this link. Refer

INSERT - SELECT with auto incrementing of datetime for each row inserted

You can use row_number and add that value as seconds to your timestamp value like this

DECLARE @value DATETIME
SET @value = CURRENT_TIMESTAMP

INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
    SELECT  
        'TESTUSER', DATEADD(SECOND, ROW_NUMBER() OVER (ORDER BY A.OPRID), @value),
        'D', A.OPRID, D.ROLENAME, 'N'
    FROM 
        PSOPRDEFN A
    INNER JOIN 
        PS_AD_X_WALK B ON B.OPRID = A.OPRID
    INNER JOIN 
        PS_JOB C ON C.EMPLID = B.GH_AD_EMPLID
    WHERE 
        B.GH_AD_EMPLID <> ''
        AND C.ACTION = 'TER'
        AND A.ACCTLOCK = 0

mysql automatically store record creation timestamp

Set the DEFAULT constraint to use CURRENT_TIMESTAMP:

CREATE TABLE ...
  your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP
  ...

For an existing table, use the ALTER TABLE statement:

ALTER TABLE your_table
ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP

Unless you specify a value to for the date_column, the default will be the date & time the INSERT statement was run. NULL and DEFAULT or valid values to use the default constraint otherwise, assuming the column is nullable.

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

---

Related Topics