Setting Creation or Change Timestamps

MariaDB/MySql: Setting CURRENT_TIMESTAMP on CREATE and changing noting on UPDATE

you can create trigger for this

DELIMITER //

CREATE TRIGGER user_prefs_before_insert
BEFORE INSERT
   ON user_prefs FOR EACH ROW

BEGIN
   SET NEW.updated = new.created;
END; //

DELIMITER ;

then another trigger for update

DELIMITER //

CREATE TRIGGER user_prefs_before_update
BEFORE UPDATE
   ON user_prefs FOR EACH ROW

BEGIN
   SET NEW.updated = CURRENT_TIMESTAMP();
END; //

DELIMITER ;

Changing the file's creation timestamp in Linux programmatically in C/C++

statx.stx_btime is filesystem-specific. Linux has only three standardized timestamps - ctime, atime, and mtime - which are filled by the filesystem-agnostic generic_fillattr function.

Creation time on the other hand is filled by filesystem-specific functions, for instance with ext4 you can see the relevant code here:

int ext4_getattr(struct user_namespace *mnt_userns, const struct path *path,
         struct kstat *stat, u32 request_mask, unsigned int query_flags)
{
    struct inode *inode = d_inode(path->dentry);
    struct ext4_inode *raw_inode;
    struct ext4_inode_info *ei = EXT4_I(inode);
    unsigned int flags;

    if ((request_mask & STATX_BTIME) &&
        EXT4_FITS_IN_INODE(raw_inode, ei, i_crtime)) {
        stat->result_mask |= STATX_BTIME;
        stat->btime.tv_sec = ei->i_crtime.tv_sec;
        stat->btime.tv_nsec = ei->i_crtime.tv_nsec;
    }
...

There seems to be no easy way to access the creation time - a quick search reveals that ext4's i_crtime is not directly modifiable.

A possible solution is to write a filesystem-specific driver to modify e.g. i_crtime directly - but this carries its own risks in modifying internal filesystem data.

Setting file creation timestamp in Java

I believe you have the following options:

1. Find a tool that does this and is callable from the command line. Then you can interact with it from your java code.
2. The following link from MSDN File Times shows how any tool would be doing it - especially note the functions GetFileTime and SetFileTime.

And here I guess you will be lucky :) Searching for those functions on Google I found a post here on SO. This answer (not the accepted one) to How to Discover a File's Creation Time with Java seems to do exactly what you want using JNA and the methods above. And if it does, then please upvote that answer one more time :)

Please don't mind the title it has a method to set the creation time too. I hope you will manage to get it working.

C++: How to modify a files 'created' timestamp?

I've never used them but i guess that you are looking for the attribute functions:

http://www.boost.org/doc/libs/1_44_0/libs/filesystem/v2/doc/reference.html#Attribute-functions



There are also functions for the last modification:

template <class Path> std::time_t last_write_time(const Path& p);
template <class Path> void last_write_time(const Path& p, const std::time_t new_time);

Having both a Created and Last Updated timestamp columns in MySQL 4.0

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

Setting/changing the ctime or Change time attribute on a file

I was able to modify the ctime with two different methods:

1. Changing the kernel so that ctime matches the mtime
2. Writing a simple (but hacky) shell script.

1st Method: Changing the kernel.

I tweaked just a few lines in KERNEL_SRC/fs/attr.c This modification updates the ctime to match the mtime whenever the mtime is "explicitly defined."

There are many ways to "explicitly define" the mtime, for example:

In Linux:

touch -m --date="Wed Jun 12 14:00:00 IDT 2013" filename

In Java (using Java 6 or 7, and presumably others):

long newModificationTime = TIME_IN_MILLIS_SINCE_EPOCH;
File myFile = new File(myPath);
newmeta.setLastModified(newModificationTime);

Here is the change to KERNEL_SRC/fs/attr.c in the notify_change function:

    now = current_fs_time(inode->i_sb);

    //attr->ia_ctime = now;  (1) Comment this out
    if (!(ia_valid & ATTR_ATIME_SET))
        attr->ia_atime = now;
    if (!(ia_valid & ATTR_MTIME_SET)) {
        attr->ia_mtime = now;
    }
    else { //mtime is modified to a specific time. (2) Add these lines
        attr->ia_ctime = attr->ia_mtime; //Sets the ctime
        attr->ia_atime = attr->ia_mtime; //Sets the atime (optional)
    }

(1) This line, uncommented, would update the ctime to the current clock time upon a change to the file. We don't want that, since we want to set the ctime ourselves. Thus, we comment this line out. (This isn't mandatory)

(2) This is really the crux of the solution. The notify_change function is executed after a file has been changed, where the time metadata needs to be updated. If no mtime was specified, then the mtime is set to the current time. Else, if the mtime was set to a specific value, we also set the ctime and the atime to that value.

2nd method: Simple (but hacky) shell script.

Brief explanation:

1. Change the system time to your target time
2. Perform a chmod on the file, file ctime now reflects target time
3. Revert the system time back.

changectime.sh

#!/bin/sh
now=$(date)
echo $now
sudo date --set="Sat May 11 06:00:00 IDT 2013"
chmod 777 $1
sudo date --set="$now"

Run this as follows:
./changectime.sh MYFILE

The file's ctime will now reflect the time in the file.

Of course, you probably don't want the file with 777 permissions. Ensure that you modify this script to your needs before using it.

When is a timestamp (auto) updated?

Give the command SHOW CREATE TABLE whatever

Then look at the table definition.

It probably has a line like this

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

You can control this default behavior when creating your table.

Or, if the timestamp column wasn't created correctly in the first place, you can change it.

ALTER TABLE whatevertable
     CHANGE whatevercolumn 
            whatevercolumn TIMESTAMP NOT NULL
                           DEFAULT CURRENT_TIMESTAMP 
                           ON UPDATE CURRENT_TIMESTAMP;

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

To prevent the column from updating when other columns change

then you need to issue this kind of update.

UPDATE whatevertable
   SET something = 'newvalue',
       whatevercolumn = whatevercolumn
 WHERE someindex = 'indexvalue'

This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.

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