CODEWITHSUNDEEP
mysql
jurchiks
jurchiks

Reputation: 1433

mysql current time with 0 seconds

I want to insert/update data into a table with the current time, but I want the seconds to be 0, for example, '2014-06-27 23:36:00'. How do I achieve this?

I think MAKETIME() could somehow be involved, but I really have no idea how.

CURDATE() + INTERVAL HOUR() HOUR + INTERVAL MINUTE() MINUTE unfortunately doesn't work, which is not surprising.

Edit: final solution - DATE_FORMAT(CURTIME(), '%Y-%m-%d %H:%i:00').

Upvotes: 1

Views: 1688

Answers (3)

Ray Perea
Ray Perea

Reputation: 5851

I know that there is already an accepted answer and I'm a bit late to the conversation, but just putting in my 2 cents for anyone it may help. This is what I use to get a timestamp with 0 seconds. Feel free to point out any flaws in my logic. =)

select CURRENT_TIMESTAMP - interval second(CURRENT_TIMESTAMP) second

Simply take the timestamp and subtract the seconds from it. This also works for datetime or timestamp columns in tables. So, if you'd like to select a timestamp column with 0 seconds, use this:

select timestamp_col - interval second(timestamp_col) second from table

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562671

mysql> select date_format(current_timestamp(), '%Y-%m-%d %H:%i:00') as ts_zero_seconds;
+---------------------+
| ts_zero_seconds     |
+---------------------+
| 2014-06-27 02:06:00 |
+---------------------+

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your idea should work with arguments to the functions:

CURDATE() + INTERVAL HOUR(now()) HOUR + INTERVAL MINUTE(now()) MINUTE

Upvotes: 2

Related Questions