CODEWITHSUNDEEP
mysqldatetime
Jaroslav Klimčík
Jaroslav Klimčík

Reputation: 4808

MySQL - function to change time in datetime

I have a column permission_to with datetime type with this value: 2016-12-31 00:00:00 and I would like to change this value to: 2016-12-31 23:59:59. I don't need update row because I need original value but the modified value I would like to use in query to compare with another column like this:

SELECT
  d.id_machine
  IF(
    d.permission_from < '2016-01-01 00:00:00',
    '2016-01-01 00:00:00',
    d.permission_from
  ) AS date_from,

  IF(
    '(HERE I NEED FUNCTION TO CHANGE DATETIME FROM 2016-12-31 00:00:00 TO 2016-12-31 23:59:59) > 2016-02-02 23:59:59',
    '2016-02-02',
    (AGAIN THE SAME FUCTION)
  ) AS date_to
FROM tbl_decision AS d

Is there any MySQL function to modify time in datetime without update?

Upvotes: 1

Views: 310

Answers (4)

fthiella
fthiella

Reputation: 49069

If permission_to is of type datetime, but always holds values like 2016-12-31 00:00:00 I would store it as a date column instead of datetime.

Anyway I would write your query as this:

SELECT
  d.id_machine,
  greatest(d.permission_from, '2016-01-01') AS date_from,
  least(d.permission_to + INTERVAL 1 DAY, '2016-02-02') AS date_to
FROM
  tbl_decision AS d

This would change your logic a litte bit, if you want to get all records for a single day, instead of this:

where
  datetimecolumn>='2016-01-01 00:00:00'
  and datetimecolumn<='2016-01-01 23:59:59'

(what happens if datetime is 2016-01-01 23:59:59.001? MySQL now supports microsecond!) just use this:

where
  datetimecolumn>='2016-01-01 00:00:00'
  and datetimecolumn<'2016-01-02 00:00:00'

Upvotes: 1

Yasser
Yasser

Reputation: 874

When we are dealing with 'end dates', we usually use the day after and then we use the 'less than' operation to compare with this date.

example: instead of 2016-12-31, we are using 2017-01-01 And when we use < so we take 2016 up to the last moment.

SELECT
  d.id_machine
  IF(
    d.permission_from < '2016-01-01 00:00:00',
    '2016-01-01 00:00:00',
    d.permission_from
  ) AS date_from,

  IF(
    DATE_ADD(d.permission_to,INTERVAL 1 DAY) > 2016-02-02 23:59:59',
    '2016-02-02',
    DATE_ADD(d.permission_to,INTERVAL 1 DAY)
  ) AS date_to
FROM tbl_decision AS d

Upvotes: 2

user5992977
user5992977

Reputation:

You can use HOUR_MINUTE format in DATE_ADD function.

DATE_ADD(permission_to, INTERVAL '23:59' HOUR_MINUTE)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could use ADDTIME:

 SELECT permission_to,
        ADDTIME(permission_to, '23:59:59') AS new_permission_to
 FROM tbl_decision;

SqlFiddleDemo

Output:

╔══════════════════════════╦═════════════════════════╗
║      permission_to       ║    new_permission_to    ║
╠══════════════════════════╬═════════════════════════╣
║ March, 02 2016 00:00:00  ║ March, 02 2016 23:59:59 ║
╚══════════════════════════╩═════════════════════════╝

Upvotes: 1

Related Questions