Mysql时间操作

Hi~晴天大圣

于 2024-06-05 20:21:32 发布

阅读量482

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/baidu_24752135/article/details/139415523

版权

数据库专栏收录该内容

18 篇文章 1 订阅

订阅专栏

一、MySql时间戳转换

select unix_timestamp();  #获取时间戳格式时间
select FROM_UNIXTIME(1717399499); #将时间戳转换为普通格式时间

二、Mysql时间相加减结果转换为秒

方法1：time_to_sec(timediff(endTime, startTime))

SELECT
  DISTINCT(column1),
  min(last_modified_time),
  max(last_modified_time),
  time_to_sec(
    timediff(max(last_modified_time), min(last_modified_time))
  ) as time1
FROM
  table1
where
  column1 = 'aaa';

方法2：timestampdiff(second, startTime, endTime)

SELECT
  DISTINCT(column1),
  min(last_modified_time),
  max(last_modified_time),
  timestampdiff(
    second,
    min(last_modified_time),
    max(last_modified_time)
  ) as time2
FROM
  table1
where
  column1 = 'aaa';

方法3：unix_timestamp(endTime) -unix_timestamp(startTime)

SELECT
  DISTINCT(column1),
  min(last_modified_time),
  max(last_modified_time),
  unix_timestamp(max(last_modified_time)) - unix_timestamp(min(last_modified_time)) as time3
FROM
  table1
where
  column1 = 'aaa';

三、Mysql时间相加减结果为时分秒

方法：timediff(time1,time2)

SELECT
  column1,
  max(last_modified_time),
  min(last_modified_time),
    timediff(
      max(last_modified_time),
      min(last_modified_time)
  )
FROM
  table1
where
  column1 = 'aaa';