TIMESTAMP on INSERT MySQL

i-CONICA
i-CONICA used Ask the Experts™
on
Hi,

I'm wondering how I can have MySQL auto fill the timestamp, preferably in epoch format when a record is inserted...

I've got the structure below...

Thanks. :D
Field	Type	        Null	Key	Default         	Extra
query	varchar(255)	YES	UNI	NULL	 
time	timestamp	NO	 	CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
results	int(11) 	YES	 	NULL

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
use unix_timestamp() instead of current_timestamp().

Author

Commented:
I don't have that option, and I'm not sure I understand? That looks like a PHP function but it isn't... How do I use this with MySQL?

I've attached the screenshot...

Thanks.
Untitled.png

Author

Commented:
I've upped the points too. :)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
Use it within the code you use
insert into table (time,column) values (unix_timestamp(),'value');
update table (time,column) values (unix_timestamp(),'value') where column='somevalue';

The type you might want to use is bigint since the value since epoch is an integer.

What application are you using to create the schema?

Commented:
i would clear the on update attribute, and leave the rest of the table definition alone.  your default value will store a date/time value in a record's time field when the record is inserted, and the time value will be specified to the second.  
Commented:
Hi,

while making insert call leave the value for "time" column.
insert statement for given table will be :-
INSERT INTO TABLE_NAME (query,results) VALUES('dummy data',1);

Open in new window

Author

Commented:
arnold,
I'm using PHPMyAdmin.
I know how to do it in PHP, but I wanted to have the SQL server generate and add the timestamp.
Is there a way to do this? Either in the query, or in the database schema for that field? It's important that the PHP server isn't generating the timestamp, has to be the SQL server.

ViaTom, I tried exactly what you said but when a record is inserted, I get 0000-00-00 00:00:00.. :(

Thanks.

Commented:
ok, so you've got the default defined and when you insert a record, a zero gets put into the time field.

i'm not familiar with phpadmin so can't help with that.  all i can suggest is to examine the table's definition using some other utility.

Author

Commented:
Solution!
Thanks. :D
Distinguished Expert 2017

Commented:
See whether you have an option within PHPmyadmin for default in a biginit type to have unix_timestamp().
The example I provided should do, instead of ignoring/omitting the column time you include it and the matching value would be the function unix_timestamp()
When the insert or update line is executed the Mysql server will execute the unix_timestamp() function and will insert the evalue since epoch january 1st 1970 GMT.
Distinguished Expert 2017

Commented:
Depending on how advanced you are with mysql/phpadmin you could use a trigger i.e. on insert time=unix_timestamp(); etc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial