CODEWITHSUNDEEP
mysqltime
Shafizadeh
Shafizadeh

Reputation: 10340

How to store number of seconds in database?

I want to store the number of seconds as a integer in the database. Something like this:

1451425369

But I cannot find a appropriate function to do that. CURRENT_TIMESTAMP, CURTIME(), NOW() and .. will store it like this:

2014-11-11 12:45:34

It should be noted, I don't access to something like PHP, because I want to do that using a TRIGGER. So I can't to use something like time(). Is there any idea?

Upvotes: 0

Views: 3559

Answers (3)

ceejayoz
ceejayoz

Reputation: 179994

1451425369 looks like a UNIX timestamp (2015-12-29 21:42 UTC). MySQL's TIMESTAMP field type stores as a UNIX timestamp under the hood, and you can retrieve that underlying timestamp value by doing something like:

SELECT UNIX_TIMESTAMP(field_name) FROM table_name;

If you need the current UNIX timestamp, UNIX_TIMESTAMP() with no arguments will give you the current integer value. You can use this in an INSERT/UPDATE without issues.

Upvotes: 4

benstopics
benstopics

Reputation: 124

It sounds like you are using a MySQL database. If you want a Unix timestamp, like the first integer value you mentioned as an example, you can use UNIX_TIMESTAMP(). If you want the number of seconds of just today, starting with 0 at 12:00am, you could store (HOUR(CURTIME())*60*60)+(MINUTE(CURTIME())*60)+SECOND(CURTIME()).

INSERT INTO ttestsec(Sec) VALUES((HOUR(CURTIME())*60*60)+(MINUTE(CURTIME())*60)+SECOND(CURTIME()));

Upvotes: 2

MojoJojo
MojoJojo

Reputation: 4232

This has been answered in another post. Pasting the response below:

CREATE TABLE `abc` (
    `pk` INT(10) NOT NULL AUTO_INCREMENT,
    `id` VARCHAR(10) NULL DEFAULT NULL,
    `timestamp` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`pk`)
)
ENGINE=InnoDB;

INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());

Upvotes: 1

Related Questions