SQL의 날짜와 함께 일하는 방법?

<p>使用标准日期类型（如DATE、DATETIME）存储日期，确保格式为YYYY-MM-DD以避免歧义；2. 常用日期函数包括提取年月日（YEAR/MONTH/DAY或EXTRACT）、获取当前时间（NOW/CURRENT_DATE）和格式化输出（DATE_FORMAT/TO_CHAR/FORMAT）；3. 通过DATE_ADD/DATE_SUB、日期加减 INTERVAL 或 DATEADD 进行日期运算，使用 >= 与 < 或 BETWEEN 过滤日期范围，利用 DATEDIFF 或日期相减计算天数差；4. 处理时区时应使用带时区的数据类型（如TIMESTAMP WITH TIME ZONE），并通过 AT TIME ZONE 或等效函数转换时区，确保跨时区比较时标准化时间。始终在日期列上建立索引并验证输入以保证查询效率和数据准确。</p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175548248444420.jpeg" class="lazy" alt="How to work with dates in SQL?"></p> <p>Working with dates in SQL is essential for querying and analyzing time-based data. Different databases (like MySQL, PostgreSQL, SQL Server, etc.) have slightly different syntax, but the core concepts are similar. Here’s a practical guide to help you handle dates effectively.</p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175548248539757.jpeg" class="lazy" alt="How to work with dates in SQL?"><hr> <h3>1. <strong>Storing and Formatting Dates</strong> </h3> <p>Most databases support common date/time data types:</p> <ul> <li> <code>DATE</code> – stores date only (e.g., <code>2024-05-20</code>)</li> <li> <code>DATETIME</code> or <code>TIMESTAMP</code> – stores date and time (e.g., <code>2024-05-20 14:30:00</code>)</li> <li> <code>TIME</code> – stores time only</li> <li> <code>YEAR</code> – stores year (in MySQL)</li> </ul> <p>Always store dates in standard formats (preferably ISO: <code>YYYY-MM-DD</code>) to avoid ambiguity.</p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175548248628588.jpeg" class="lazy" alt="How to work with dates in SQL?"><p><strong>Example:</strong></p><pre class='brush:php;toolbar:false;'>CREATE TABLE events ( id INT, event_name VARCHAR(100), event_date DATE );</pre><p>Insert a date:</p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175548248725337.jpeg" class="lazy" alt="How to work with dates in SQL?" /><pre class='brush:php;toolbar:false;'>INSERT INTO events VALUES (1, 'Meeting', '2024-05-20');</pre><hr /><h3 id="strong-Common-Date-Functions-strong">2. <strong>Common Date Functions</strong></h3><p>Here are widely used functions across databases (with slight variations):</p><h4 id="Extract-parts-of-a-date">Extract parts of a date</h4><p>Get year, month, day, etc., from a date.</p><pre class='brush:php;toolbar:false;'>-- MySQL / PostgreSQL SELECT YEAR(event_date) AS year, MONTH(event_date) AS month, DAY(event_date) AS day FROM events;</pre><p>In PostgreSQL, use <code>EXTRACT</code>:</p><pre class='brush:php;toolbar:false;'>SELECT EXTRACT(YEAR FROM event_date) AS year, EXTRACT(MONTH FROM event_date) AS month FROM events;</pre><h4 id="Get-current-date-and-time">Get current date and time</h4><pre class='brush:php;toolbar:false;'>-- MySQL SELECT NOW(); -- date and time SELECT CURDATE(); -- date only -- PostgreSQL SELECT NOW(); -- date and time SELECT CURRENT_DATE; -- date only -- SQL Server SELECT GETDATE(); -- date and time SELECT CAST(GETDATE() AS DATE); -- date only</pre><h4 id="Format-dates">Format dates</h4><p>Convert a date to a custom string format.</p><pre class='brush:php;toolbar:false;'>-- MySQL SELECT DATE_FORMAT(event_date, '%M %d, %Y') FROM events; -- Result: May 20, 2024 -- PostgreSQL SELECT TO_CHAR(event_date, 'FMMonth DD, YYYY') FROM events; -- SQL Server SELECT FORMAT(event_date, 'MMMM dd, yyyy') FROM events;</pre><hr /><h3 id="strong-Date-Arithmetic-and-Comparisons-strong">3. <strong>Date Arithmetic and Comparisons</strong></h3><p>You’ll often need to add or subtract time, or filter by date ranges.</p><h4 id="Add-or-subtract-days-months">Add or subtract days/months</h4><pre class='brush:php;toolbar:false;'>-- MySQL SELECT DATE_ADD(event_date, INTERVAL 7 DAY) FROM events; SELECT DATE_SUB(event_date, INTERVAL 1 MONTH) FROM events; -- PostgreSQL SELECT event_date + INTERVAL '7 days' FROM events; SELECT event_date - INTERVAL '1 month' FROM events; -- SQL Server SELECT DATEADD(day, 7, event_date) FROM events; SELECT DATEADD(month, -1, event_date) FROM events;</pre><h4 id="Filter-by-date-ranges">Filter by date ranges</h4><pre class='brush:php;toolbar:false;'>-- Events in May 2024 SELECT * FROM events WHERE event_date >= '2024-05-01' AND event_date < '2024-06-01'; -- Or using BETWEEN (inclusive) SELECT * FROM events WHERE event_date BETWEEN '2024-05-01' AND '2024-05-31';</pre><p>Use <code>>=</code> and <code><</code> for time precision when including timestamps.</p><h4 id="Days-between-two-dates">Days between two dates</h4><pre class='brush:php;toolbar:false;'>-- MySQL SELECT DATEDIFF('2024-05-30', '2024-05-20'); -- 10 days -- PostgreSQL SELECT ('2024-05-30'::date - '2024-05-20'::date); -- 10 -- SQL Server SELECT DATEDIFF(day, '2024-05-20', '2024-05-30');</pre><hr /><h3 id="strong-Handling-Time-Zones-Advanced-strong">4. <strong>Handling Time Zones (Advanced)</strong></h3><p>Some databases support time zone-aware types like <code>TIMESTAMP WITH TIME ZONE</code> (PostgreSQL) or <code>DATETIMEOFFSET</code> (SQL Server).</p><pre class='brush:php;toolbar:false;'>-- PostgreSQL SELECT NOW() AT TIME ZONE 'UTC'; SELECT NOW() AT TIME ZONE 'America/New_York';</pre><p>Be cautious when comparing timestamps across time zones—always standardize if needed.</p> <hr> <h3 id="Key-Tips">Key Tips:</h3> <ul> <li>Always use proper date types instead of strings.</li> <li>Be consistent with time zones in applications.</li> <li>Use indexes on date columns for faster queries.</li> <li>Validate input dates to avoid errors.</li> </ul> <hr> <p>Working with dates doesn’t have to be hard—once you know the right functions and patterns, filtering, formatting, and calculating time intervals becomes straightforward. Just remember to check your specific database’s documentation for syntax differences.</p>

위 내용은 SQL의 날짜와 함께 일하는 방법?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!