MySQL时间戳转日期格式教程 where查询时间范围筛选指南

<blockquote>掌握MySQL时间戳转换与筛选需用FROM_UNIXTIME()和UNIX_TIMESTAMP()进行高效转换，优先在WHERE条件右侧转换时间值以利用索引，避免对字段使用函数导致全表扫描；同时区分DATETIME与TIMESTAMP的时区处理差异，建议统一使用UTC时间存储，结合CONVERT_TZ()或应用层处理时区转换，确保数据一致性与查询性能。</blockquote> <p><img src="https://img.php.cn/upload/article/001/503/042/175547946383175.jpeg" alt="mysql时间戳转日期格式教程 where查询时间范围筛选指南"></p> <p>在MySQL里处理时间数据，尤其是当它们以时间戳（Unix timestamp）的形式存在时，如何高效地转换为可读的日期格式，并在此基础上进行精确的时间范围筛选，这确实是每一个开发者都会遇到的实际问题。我个人的经验是，掌握好这几招，能少走不少弯路，也能让你的数据查询变得既快又准。</p> <p>要解决时间戳转换和时间范围筛选的问题，核心在于灵活运用MySQL提供的日期时间函数。最直接的方案是利用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FROM_UNIXTIME()</pre></div>将时间戳转为日期时间类型，然后配合<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句中的比较操作符进行筛选。如果你的日期字段本身就是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>类型，那筛选起来会更直接，甚至可以利用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE()</pre></div>函数来忽略时间部分，只比较日期。</p> <h3> <a style="color:#f60; text-decoration:underline;" title="为什么" href="https://www.php.cn/zt/92702.html" target="_blank">为什么</a>我们需要在MySQL中转换时间戳？以及常用的转换函数有哪些？</h3> <p>说实话，我刚开始接触这块儿的时候，总觉得直接存<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>省事，毕竟它看起来更直观。但有些场景下，比如日志系统、数据同步或者跨语言交互时，Unix时间戳的简洁性和标准化反而成了优点。它就是一个简单的整数，不涉及时区问题（当然，这是相对的，它代表的是自UTC 1970年1月1日0时0分0秒以来的秒数），便于传输和存储。但当我们想给人看或者做报表分析时，谁愿意盯着一串数字发呆呢？所以，转换是必须的。</p> <p>MySQL提供了几个核心函数来处理这种转换：</p> <ul> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FROM_UNIXTIME(unix_timestamp, [format])</pre></div></strong>: 这是最常用的，它能把一个Unix时间戳（通常是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INT</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BIGINT</pre></div>类型）转换成<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>类型的值，或者按照你指定的格式输出字符串。</p> <ul> <li>如果你只传时间戳，它会返回<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>类型：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT FROM_UNIXTIME(1678886400); -- 结果：'2023-03-15 08:00:00'</pre></div></li> <li>如果你想直接得到特定格式的字符串，可以加上<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">format</pre></div>参数：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT FROM_UNIXTIME(1678886400, '%Y-%m-%d %H:%i:%s'); -- 结果：'2023-03-15 08:00:00' SELECT FROM_UNIXTIME(1678886400, '%Y年%m月%d日'); -- 结果：'2023年03月15日'</pre></div><p>这里面的格式符和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE_FORMAT()</pre></div>是通用的，非常灵活。</p> </li> </ul> </li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNIX_TIMESTAMP([datetime_expression])</pre></div></strong>: 这个函数是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FROM_UNIXTIME()</pre></div>的反向操作，它能把一个日期时间值（比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>字段或者日期字符串）转换成Unix时间戳。</p> <ul> <li>如果你不传参数，它返回当前UTC时间戳。</li> <li>如果你传入日期时间值，它返回对应的时间戳：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT UNIX_TIMESTAMP('2023-03-15 08:00:00'); -- 结果：1678886400 SELECT UNIX_TIMESTAMP(NOW()); -- 结果：当前时间戳</pre></div><p>在进行时间范围筛选时，这个函数尤其有用，我们稍后会详细讲。</p> </li> </ul> </li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE_FORMAT(date, format)</pre></div></strong>: 虽然这个不是直接用来转换时间戳的，但它在格式化任何日期时间类型的值时都非常强大。当你已经把时间戳转成了<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>类型，但又想以特定格式展示时，它就派上用场了。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT DATE_FORMAT(FROM_UNIXTIME(1678886400), '%Y-%m-%d'); -- 结果：'2023-03-15'</pre></div><p>这几种转换方式，真的是得心应手才行，它们是处理MySQL时间数据的基石。</p> </li> </ul> <h3>如何在WHERE子句中高效筛选特定时间范围内的数据？</h3> <p>筛选数据，尤其是在处理大量数据时，性能是王道。我见过太多因为在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句里对字段本身用了函数，导致全表扫描的案例，那真是欲哭无泪。记住一个核心原则：<strong>尽量避免在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句的左侧（即你要查询的列）使用函数，因为它可能会导致索引失效。</strong></p> <p>我们分两种常见情况来说：</p> <p><strong>1. 你的时间列是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>类型：</strong> 这是最理想的情况，因为这类字段通常会建立索引。</p> <ul> <li> <p><strong>直接比较：</strong> 这是最高效的方式。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 查询2023年3月15日当天的数据 SELECT * FROM your_table WHERE create_time >= '2023-03-15 00:00:00' AND create_time < '2023-03-16 00:00:00'; -- 或者使用 BETWEEN...AND... SELECT * FROM your_table WHERE create_time BETWEEN '2023-03-15 00:00:00' AND '2023-03-15 23:59:59';</pre></div><p>个人建议用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">>=</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><</pre></div>的组合，这样可以避免边界问题，比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">23:59:59</pre></div>可能漏掉最后一秒的数据。</p> </li> <li> <p><strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE()</pre></div>函数（慎用）：</strong> 如果你只想按日期部分筛选，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE(column)</pre></div>看起来很方便。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM your_table WHERE DATE(create_time) = '2023-03-15';</pre></div><p>但问题来了，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE(create_time)</pre></div>会对<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">create_time</pre></div>列应用函数，这通常会导致索引失效，变成全表扫描。对于小表可能无所谓，但对于大表，这就是性能杀手。所以，我更倾向于用上面那种<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">>=</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><</pre></div>的范围查询来替代。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/ai/1085"> <img src="https://img.php.cn/upload/ai_manual/000/000/000/175680082584830.jpg" alt="腾讯智影"> </a> <div class="aritcle_card_info"> <a href="/ai/1085">腾讯智影</a> <p>腾讯推出的在线智能视频创作平台</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="腾讯智影"> <span>341</span> </div> </div> <a href="/ai/1085" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="腾讯智影"> </a> </div> </li> </ul> <p><strong>2. 你的时间列是Unix时间戳（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INT</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BIGINT</pre></div>类型）：</strong> 这种情况下，你的列存的是整数。要筛选时间范围，最聪明的做法是把比较值转换成Unix时间戳，而不是把列本身转换成日期时间。</p> <ul> <li> <p><strong>将比较值转换为Unix时间戳：</strong></p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 查询2023年3月15日当天的数据，假设 timestamp_col 是 Unix 时间戳 SELECT * FROM your_table WHERE timestamp_col >= UNIX_TIMESTAMP('2023-03-15 00:00:00') AND timestamp_col < UNIX_TIMESTAMP('2023-03-16 00:00:00');</pre></div><p>这种方式下，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">timestamp_col</pre></div>列保持原样，如果它有索引，那么索引就能被有效地利用起来，查询效率会很高。</p> </li> <li> <p><strong>避免这种做法（会使索引失效）：</strong></p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 错误示范：对 timestamp_col 应用了 FROM_UNIXTIME 函数 SELECT * FROM your_table WHERE FROM_UNIXTIME(timestamp_col) >= '2023-03-15 00:00:00' AND FROM_UNIXTIME(timestamp_col) < '2023-03-16 00:00:00';</pre></div><p>虽然结果是对的，但性能会非常差，因为它需要对每一行数据都进行<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FROM_UNIXTIME</pre></div>转换，然后才能进行比较。</p> </li> </ul> <p>总结一下，无论你的时间字段是什么类型，核心思想都是：让你的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句左侧的字段保持“干净”，让索引能够发挥作用。</p> <h3>处理时区问题对MySQL时间戳和日期查询的影响是什么？</h3> <p>时区，这玩意儿一不小心就能把你搞得头大。尤其是在全球化的应用中，或者当你的数据库服务器和应用服务器不在同一个时区时，它就成了个隐藏的坑。</p> <p>MySQL中，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>这两种类型对时区的处理方式截然不同：</p> <ul> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div></strong>: 这是一个“傻瓜式”的类型。你存什么，它就存什么，完全不涉及时区转换。比如你存了<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">'2023-03-15 08:00:00'</pre></div>，它就老老实实地存这个字符串，不管你服务器是哪个时区。读取时也一样，原样返回。这对于避免意外的时区转换非常有用，但如果你需要处理不同时区的用户数据，这就意味着你需要自己在应用层处理时区转换。</p></li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div></strong>: 这个类型就“智能”多了，但也更容易让人迷惑。它存储的是UTC时间（协调世界时），但当你插入数据时，它会根据当前的MySQL会话时区（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">@@time_zone</pre></div>）将输入值转换为UTC存储；当你查询时，它又会根据当前会话时区将存储的UTC值转换回你设定的时区显示。</p> <ul> <li>举个例子：如果你的MySQL服务器时区是UTC，你插入<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">'2023-03-15 08:00:00'</pre></div>到一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>列，它会直接存储对应的UTC时间戳。但如果你的会话时区是东八区（+08:00），你插入<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">'2023-03-15 08:00:00'</pre></div>，MySQL会先把它理解为东八区的8点，然后转换为UTC的0点（因为东八区比UTC早8小时），再存储对应的UTC时间戳。查询时，如果你的会话时区还是东八区，它会把存储的UTC时间戳再转回东八区显示。</li> <li>这就意味着，同一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>值，在不同会话时区下查询，可能会看到不同的显示结果。</li> </ul> </li> </ul> <p><strong>实际影响和应对策略：</strong></p> <p>我个人偏向于在数据库层面统一使用UTC时间，减少混乱。</p> <ol> <li> <p><strong>统一使用UTC：</strong></p> <ul> <li>如果你用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>，那么就约定好所有存储的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>值都是UTC时间。</li> <li>如果你用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>，那就确保你的MySQL服务器和应用程序都配置为UTC时区，或者至少在会话开始时<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SET time_zone = '+00:00'</pre></div>。这样，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>的自动转换就不会带来意外了，因为存取都是基于UTC。</li> <li>在应用程序层面，将用户输入的时间转换为UTC再插入数据库，从数据库读取UTC时间后再转换为用户所在的时区显示。</li> </ul> </li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">CONVERT_TZ(dt, from_tz, to_tz)</pre></div>函数：</strong> 如果你确实需要在SQL层面进行时区转换，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">CONVERT_TZ()</pre></div>函数能帮上忙。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 将一个东八区的时间转换为UTC时间 SELECT CONVERT_TZ('2023-03-15 08:00:00', '+08:00', '+00:00'); -- 结果：'2023-03-15 00:00:00'</pre></div><p>但这个函数需要MySQL的时区信息表（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">mysql.time_zone_name</pre></div>等）是完整的，否则会返回<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NULL</pre></div>。</p> </li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOW()</pre></div> vs. <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UTC_TIMESTAMP()</pre></div>：</strong></p> <ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOW()</pre></div>返回的是当前MySQL会话时区的日期时间。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UTC_TIMESTAMP()</pre></div>返回的是当前的UTC日期时间。 在记录创建时间或更新时间时，我通常会选择<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UTC_TIMESTAMP()</pre></div>，这样无论服务器在哪个时区，记录的时间都是统一的基准。</li> </ul> </li> </ol> <p>处理时区问题，关键在于理解<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">TIMESTAMP</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATETIME</pre></div>的特性差异，并根据你的应用场景选择最适合的策略。最怕的就是稀里糊涂地混用，最后数据对不上，查起来简直是噩梦。</p>

以上就是MySQL时间戳转日期格式教程 where查询时间范围筛选指南的详细内容，更多请关注php中文网其它相关文章！