CODEWITHSUNDEEP
phpmysqltimeformat
enigmaticus
enigmaticus

Reputation: 548

How to format now function in mysql

Is there a way to format mysql NOW() So i would get something like year month day hours minutes seconds, all together, like this:

20130822143222

or to get something like microtime() in PHP, but for MySQL?

I need it for the trigger before insert. Thank you :)

Upvotes: 2

Views: 22602

Answers (8)

Apurv
Apurv

Reputation: 123

In Mysql use Now() function to date & DATE_FORMAT function sql .

select DATE_FORMAT(NOW(),'%d/%m/%Y %H:%i:%s') as date;;

Output:

date

24/01/2024 15:54:32

Upvotes: 0

user1502952
user1502952

Reputation: 1420

Using EXTRACT function:

"Select EXTRACT(YEAR from NOW()) as year, EXTRACT(MONTH from NOW()) as month, EXTRACT(DAY
from NOW()) as day, EXTRACT(HOUR from NOW()) as hour, EXTRACT(MINUTE from NOW()) as  
minute, EXTRACT(SECOND from NOW()) as second, EXTRACT(MICROSECOND from NOW()) as 
microsecond, EXTRACT(MINUTE_MICROSECOND from NOW()) as minute_microsecond";

Upvotes: 0

Jost
Jost

Reputation: 1534

I think this should work:

select UNIX_TIMESTAMP(NOW());

*Jost

Upvotes: 0

insertusernamehere
insertusernamehere

Reputation: 23610

Besides DATE_FORMAT this should work too: SUBSTRING(NOW() + 0, 1, 8).

Upvotes: 0

Goutam Pal
Goutam Pal

Reputation: 1763

select date_format(NOW(),'%Y%m%d%H%i%s') as date_val

Upvotes: 2

Carsten Massmann
Carsten Massmann

Reputation: 28246

SELECT DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')

Upvotes: 13

Mihai
Mihai

Reputation: 26804

Try:

DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s')

Upvotes: 1

dnet
dnet

Reputation: 1429

You could use string replace functions to remove dashes, colons and spaces from the output of NOW() as it just returns a string that has exactly what you want, separated by punctuation is listed above. See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Upvotes: 0

Related Questions