The key to processing date and time data is to master common functions and their differences in different databases. 1. Get the current time: MySQL uses NOW(), PostgreSQL supports NOW() and CURRENT_TIME, SQL Server uses GETDATE() or SYSDATETIME(); 2. Extract the date part: MySQL uses YEAR(), MONTH() and other functions, PostgreSQL recommends EXTRACT(), SQL Server uses DATEPART(); 3. Date operation: MySQL is implemented through DATE_ADD() or INTERVAL, PostgreSQL uses INTERVAL, SQL Server uses DATEADD(); 4. Format output: MySQL uses DATE_FORMAT(), PostgreSQL uses TO_CHAR(), and SQL Server uses FORMAT(). These functions are particularly practical in report generation.

Processing date and time data is a very common task in SQL, especially when doing reports, analysis, or system log processing. The key is to master several commonly used functions and understand their differences in different databases.

Get the current time and date

Most databases provide functions that get the current time, such as NOW() , CURRENT_DATE , or GETDATE() , depending on which database you are using. These functions can directly return the server's current timestamp.

• MySQL : NOW() returns the full date and time format, including year, month, day, hour, minute and second.
• PostgreSQL : Use NOW() too, but you can also use CURRENT_TIME to only take the time part.
• SQL Server : Use GETDATE() to get the current time, while SYSDATETIME() will be more accurate.

If you only need today's dates, consider using CURRENT_DATE (MySQL and PostgreSQL support), or combine CAST() to convert the full time to a retained date only.

Extract some information from date and time

Many times we do not need a complete date and time, but want to extract a part of it, such as year, month, hour, etc. At this time, you can use the EXTRACT() function, or special functions such as YEAR() and MONTH() .

• MySQL supports YEAR(date) , MONTH(date) , DAYOFWEEK(date) , etc.
• PostgreSQL recommends using EXTRACT(YEAR FROM date) syntax
• SQL Server is generally extracted using DATEPART(year, date)

For example, if you want to count the order quantity per month, you can first extract the months in the order date and then group them into groups and summarize them by month.

Date calculation and interval processing

It is also common to add or subtract dates, such as finding records for the last 7 days, or calculating the difference in the number of days between two dates.

Different databases have different writing methods:

• MySQL: DATE_ADD(now(), INTERVAL 1 DAY) or NOW() INTERVAL 1 WEEK
• PostgreSQL: now() INTERVAL '1 day'
• SQL Server: DATEADD(DAY, 1, GETDATE())

To calculate how many days, hours, or minutes the difference between two dates, you can use the DATEDIFF() function, but be aware that its parameter order may vary from database to database.

In addition, some databases support "direct subtraction" to get the difference in day number. For example, in PostgreSQL, you can directly write end_date - start_date to get the difference in day number.

Date format output

Sometimes you need to display the date in a specific string format, such as "2024-08-05" or "Monday", and you need to use the formatting function.

• MySQL: DATE_FORMAT(date, '%Y-%m-%d')
• PostgreSQL: TO_CHAR(date, 'YYYY-MM-DD')
• SQL Server: FORMAT(date, 'yyyy-MM-dd')

This feature is especially useful when generating reports, especially if you want the date to be displayed by the user's region.

Basically that's it. Although each database has slightly different implementations, as long as you master the basic logic, you can flexibly respond to various date and time-related query needs.

The above is the detailed content of Working with Date and Time Data Using SQL Functions. For more information, please follow other related articles on the PHP Chinese website!