0

I am new on MySQL and I don't know how to solve this

I have a table that contains datetime type. The coloumn's format is yyyy-mm-dd hh:mm:ss, like this 2015-12-02 20:21:54. I want to remove the second.

If the second <= 30 then remove the second

2015-12-02 20:00:15 ---> 2015-12-02 20:00

If the second > 30 then minute + 1

2015-12-02 20:21:54 ---> 2015-12-02 20:22

Does anyone know how to make query like this?

Chetan Ameta
  • 7,696
  • 3
  • 29
  • 44
Barata
  • 9
  • 1
  • 3
    Possible duplicate of [Rounding a DATETIME value in MySQL to nearest minute](http://stackoverflow.com/questions/25747539/rounding-a-datetime-value-in-mysql-to-nearest-minute) – Chetan Ameta Dec 16 '15 at 08:52

4 Answers4

0

use DATE_FORMAT

SELECT 
if(DATE_FORMAT('2015-12-02 20:00:55','%s') <= 30,
DATE_FORMAT('2015-12-02 20:00','%Y-%m-%d %H:%i'),
DATE_FORMAT('2015-12-02 20:00','%Y-%m-%d %H:%i') + INTERVAL 1 MINUTE)

Demo

Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
0

Does something like this work?

SELECT
    DATE_FORMAT(
        DATE_ADD(
            DATE_FORMAT(your_date, '%Y-%m-%d %H:%i:00'),
            INTERVAL IF (SECOND(your_date) < 30, 0, 1) MINUTE
        ),
        '%Y-%m-%d %H:%i'
    ),
Dezza
  • 1,094
  • 4
  • 22
  • 25
0

I'd probably do something like this (untested):

SELECT CASE SECOND(@yourDate) > 30 THEN DATE_ADD(@yourDate INTERVAL SECOND(@yourDate) SECOND) ELSE @yourDate;

You may wish to read the date and time functions part of the mysql manual.

Kris
  • 40,604
  • 9
  • 72
  • 101
0

UPDATE table_name SET
date_column=(CASE WHEN SECOND(date_column)>=30 THEN DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:00')+INTERVAL 1 MINUTE
ELSE DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:00')
END);

Obaidul
  • 57
  • 5