SQL中日期格式处理方法大全

2024-03-01 0 812
目录
  • 背景:
  • 一、获取当前日期
    • MySQL
    • Hive
    • sql server
  • 二、日期格式化
    • MySQL
    • Hive
    • sql server
  • 三、日期转换为字符串
    • MySQL
    • Hive
    • sql server
    • 日期计算
  • 总结

    背景:

    实际工作,使用SQL语句对数据进行处理,有一大部分工作是对日期时间型数据进行处理,通过对字段的拼接或转换生成实际需要的格式的日期字段。本文章尽可能全面记录现在主流的数据库(MySQL和Hive)对日期格式的处理,形成一份工作速查文档,提升工作效率。

    一、获取当前日期

    MySQL

    select now(); –日期时间格式
    select current_date(); — 日期格式
    select current_time(); — 时间格式
    select sysdate(); — 日期时间格式
    select current_timestamp(); — 日期时间戳

    Hive

    select current_date() — 日期格式
    select current_timestamp() — 日期时间格式

    sql server

    select getdate()
    –获得年月日
    select convert(nvarchar(10),getdate(),120)
    –获得年月
    select convert(nvarchar(7),getdate(),120)

    二、日期格式化

    格式化可以将不符合要求规范的日期格式字段或字符串类型字段转换为格式化日期。

    MySQL

    select date_format(\’20230613\’,\’%Y-%m-%d\’);
    select date_format(\’20230613\’,\’%Y-%m-%d %H:%i:%s\’);
    select date_format(now(),\’%Y-%m-%d\’);
    — 可使用unix_timestamp函数获取 unix 时间戳,使用时间格式转换函数from_unixtime转换为格式日期
    select from_unixtime(unix_timestamp(\’20230613\’), \’%Y-%m-%d\’);

    Hive

    select date_format(current_timestamp(),\’yyyy-MM-dd\’)
    — Hive中对月份格式用MM
    select date_format(current_timestamp(),\’yyyy-MM-dd HH:mm:ss\’)
    select from_unixtime(unix_timestamp(\’2023-06-13\’, \’yyyy-MM-dd\’), \’yyyyMMdd\’) — 生成20230613对应格式的日期
    — unix_timestamp返回对应时间戳
    select unix_timestamp() — 返回当前时间对应的时间戳,时间戳数值可以加减,如一小时3600秒,一天86400秒

    sql server

    select convert(datetime,\’YYYY-MM-DD HH24:MI:SS\’)
    — cast强制转换
    select cast(\’YYYY-MM-DD HH24:MI:SS\’ as datetime)

    三、日期转换为字符串

    SQL表中存储日期对数据类型有要求,特别是 hive存储日期时间的时候通常用string,所以有时也要实现日期转换为字符串。

    MySQL

    — 可使用substr字符串切割函数,返回从m开始长度为n的字符串
    select substr(now(),1,10);
    — 使用concat字符串拼接函数
    select concat(substr(now(),1,4),substr(now(),6,2));

    Hive

    — 使用cast强制转换为字符串
    select cast(from_unixtime(unix_timestamp(\’20230613\’, \’yyyyMMdd\’), \’yyyy-MM-dd\’) as string)

    sql server


    select convert(nvarchar(10),getdate(),120)
    — 常用格式
    Select CONVERT(varchar(100), GETDATE(), 120) — 年月日 时分秒
    Select CONVERT(varchar(100), GETDATE(), 112) — 年月日(无分隔符)
    Select CONVERT(varchar(100), GETDATE(), 111) — 年月日(分隔符/)
    Select CONVERT(varchar(100), GETDATE(), 102) — 年月日(分隔符.)
    Select CONVERT(varchar(100), GETDATE(), 23) — 年月日(分隔符-)
    Select CONVERT(varchar(100), GETDATE(), 8) — 时分秒

    日期计算

    在表的实际计算中,常需要计算距离某个日期或现在日期往前或往后某段时间的日期。

    — 加(Mysql)
    select date_add(now(),interval 1 day);
    — year:年,quarter:季,month:月,week:周,day:天,hour:小时,minuter:分钟,second:秒,microsecond:毫秒
    — 加(sql server)
    select dateadd(day,n,\’2023-06-20\’)
    — 加(Hive)
    select date_add(current_timestamp(),1) — 加一天
    select date_format(date_sub(current_timestamp(),1),\’yyyy-MM-dd\’) –指定前一天日期
    select from_unixtime((unix_timestamp()-86400),\’yyyy-MM\’) –指定前一天日期
    — 减
    select date_sub(now(),interval 1 month);
    select date_sub(date_format(current_timestamp(),\’yyyy-MM-dd\’),1)
    select date_sub(current_timestamp(),14) — Hive指定前一天日期
    — 计算日期间隔
    select datediff(\’2023-6-16\’,\’2023-6-1\’) — mysql计算两个日期之间的间隔(相差天数)
    select datediff(\’d\’,\’2023-6-1\’,\’2023-6-16 12:20:00\’) –sql server计算两个日期之间间隔天数
    select timediff(\’12:00:00\’, \’11:30:00\’) — 两个时间间隔
    select timestampdiff(month,date_sub(now(),interval 60 day),now()); — 相差月
    — timestampdiff第一个参数定义返回计算的结果。year:年,quarter:季,month:月,week:周,day:天,hour:小时,minuter:分钟,second:秒,microsecond:毫秒
    — timestampdiff减去的日期带有时间,会认为是下一天(2023-07-20 08:00:00会按照2023-07-21计算);datediff会直接截取时间(2023-07-20 18:00:00也会按照2023-07-20计算)
    select timestampdiff(day,\’2023-07-13\’,current_date()) — timestampdiff可以实现各种字符串(例如2023-07-13,20230703)日期的加减
    — 当月的第一天
    select STR_TO_DATE(CONCAT(DATE_FORMAT(now(),\’%Y-%m\’), \’-01\’), \’%Y-%m-%d\’)
    — sql server返回当月1号
    select CONVERT(varchar(10),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0),120)
    — 当月最后一天
    select DATE_ADD(STR_TO_DATE(CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL 1 MONTH),\’%Y-%m\’), \’-01\’), \’%Y-%m-%d\’),INTERVAL -1 DAY);

    总结

    到此这篇关于SQL中日期格式处理的文章就介绍到这了,更多相关SQL日期格式处理内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源!

    收藏 (0) 打赏

    感谢您的支持,我会继续努力的!

    打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
    点赞 (0)

    悠久资源 数据库其它 SQL中日期格式处理方法大全 https://www.u-9.cn/database/dataqita/182562.html

    常见问题

    相关文章

    发表评论
    暂无评论
    官方客服团队

    为您解决烦忧 - 24小时在线 专业服务