CODEWITHSUNDEEP
phpmysqlsqldatedatetime
alchuang
alchuang

Reputation: 3571

PHP/SQL Date Created vs Date Modified

I am working on a function that compares the date created and date modified of images and return the status of each case with PHP + MySQL. However, I realized that the data i'm trying to compare both end up using the CURRENT_TIMESTAMP in MySQL so whenever they are updated they end up having the same dates.

Is there a way to just only save the first date the data is inserted into the database (date created) so it doesn't change based on date modified?

Any help is appreciated, thanks in advance!

UPDATE:

my timestamp columns are configured using "DEFAULT CURRENT_TIMESTAMP" not the "ON UPDATE CURRENT_TIMESTAMP" option. Any other work arounds?

UPDATE2:

Please see below for my table definition.

CREATE TABLE IF NOT EXISTS `images` (
  `id` varchar(50) NOT NULL,
  `patientid` varchar(8) NOT NULL,
  `caseid` varchar(25) NOT NULL,
  `image_name` varchar(256) NOT NULL,
  `status` int(1) unsigned NOT NULL,
  `comments` varchar(4000) DEFAULT NULL,
  `mod_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 2

Views: 1379

Answers (1)

Tobias
Tobias

Reputation: 1682

Seems like your timestamp columns are configured with "ON UPDATE CURRENT_TIMESTAMP" option, which automatically updates them.

As there does not seem a way to change this on a column, you have to create a new column without that option.

See the TIMESTAMP manual for details visit timestamp-initialization

Upvotes: 1

Related Questions