How to get the current date and time in SQL?
在 SQL 中获取当前日期和时间的方法因数据库系统而异，常见方式如下：1. MySQL 和 MariaDB 使用 NOW() 或 CURRENT_TIMESTAMP，可用于查询、插入及设置默认值；2. PostgreSQL 使用 NOW()，也可用 CURRENT_TIMESTAMP 或类型转换去除时区；3. SQL Server 使用 GETDATE() 或 SYSDATETIME()，支持插入和默认值设定；4. Oracle 使用 SYSDATE 或 SYSTIMESTAMP，需注意日期格式转换。掌握这些函数可在不同数据库中灵活处理时间相关操作。
获取当前日期和时间在 SQL 中是一个常见的需求，比如用于记录数据插入时间、筛选最近的数据等。不同数据库系统语法略有不同，但基本都能用类似函数实现。
MySQL 和 MariaDB：使用 NOW() 或 CURRENT_TIMESTAMP
在 MySQL 或 MariaDB 中，最常用的方式是使用 NOW() 函数：
SELECT NOW();
这个语句会返回当前的日期和时间，格式通常是 YYYY-MM-DD HH:MM:SS。你也可以在插入数据时直接使用它：
INSERT INTO my_table (created_at) VALUES (NOW());
另一个替代方法是 CURRENT_TIMESTAMP，它的使用方式类似，有时候在定义表结构时会用到默认值设置：
CREATE TABLE my_table (
id INT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);PostgreSQL：使用 NOW()
PostgreSQL 也支持 NOW() 函数来获取当前时间戳：
SELECT NOW();
返回的结果包括时区信息，如果你只想要不带时区的时间，可以使用：
SELECT CURRENT_TIMESTAMP;
或者使用类型转换去掉时区部分：
SELECT NOW()::TIMESTAMP;
在实际开发中，根据是否需要考虑时区来决定具体用哪个函数。
SQL Server：使用 GETDATE() 或 SYSDATETIME()
在 SQL Server 中，获取当前时间的标准函数是 GETDATE()：
SELECT GETDATE();
它返回的是 datetime 类型，精度到毫秒。如果你需要更高精度的时间（例如纳秒级），可以用：
SELECT SYSDATETIME();
这两个函数都可以在插入或更新数据时使用：
插入当前时间：
INSERT INTO my_table (log_time) VALUES (GETDATE());
设置默认值：
ALTER TABLE my_table ADD CONSTRAINT DF_my_table_log_time DEFAULT GETDATE() FOR log_time;
Oracle：使用 SYSDATE 和 SYSTIMESTAMP
Oracle 使用 SYSDATE 获取当前日期和时间：
SELECT SYSDATE FROM dual;
如果你想获得更高的精度（包括时区信息），可以使用：
SELECT SYSTIMESTAMP FROM dual;
Oracle 的时间处理比较严格，注意日期类型不同时可能需要显式转换，比如使用 TO_CHAR() 转换为字符串格式输出。
基本上就这些常见数据库中的做法了。虽然写法不同，但目的都是一样的：拿到当前的时间点。只要记住不同数据库的关键函数，就能灵活应用在查询、插入和逻辑判断中。
The above is the detailed content of How to get the current date and time in SQL?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
How to find columns with a specific name in a SQL database?
Jul 07, 2025 am 02:08 AM
To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf
What is the difference between SQL and NoSQL
Jul 08, 2025 am 01:52 AM
The core difference between SQL and NoSQL databases is data structure, scaling method and consistency model. 1. In terms of data structure, SQL uses predefined patterns to store structured data, while NoSQL supports flexible formats such as documents, key values, column families and graphs to process unstructured data; 2. In terms of scalability, SQL usually relies on stronger hardware on vertical expansion, while NoSQL realizes distributed expansion through horizontal expansion; 3. In terms of consistency, SQL follows ACID to ensure strong consistency and is suitable for financial systems, while NoSQL mostly uses BASE models to emphasize availability and final consistency; 4. In terms of query language, SQL provides standardized and powerful query capabilities, while NoSQL query languages are diverse but not as mature and unified as SQL.
Advantages of Using Common Table Expressions (CTEs) in SQL.
Jul 07, 2025 am 01:46 AM
The main advantages of CTEs in SQL queries include improving readability, supporting recursive queries, avoiding duplicate subqueries, and enhancing modular and debugging capabilities. 1. Improve readability: By splitting complex queries into multiple independent logical blocks, the structure is clearer; 2. Support recursive queries: The logic is simpler when processing hierarchical data, suitable for deep traversal; 3. Avoid duplicate subqueries: define multiple references at a time, reduce redundancy and improve efficiency; 4. Better modularization and debugging capabilities: Each CTE block can be run and verified separately, making it easier to troubleshoot problems.
When to use SQL subqueries versus joins for data retrieval.
Jul 14, 2025 am 02:29 AM
Whether to use subqueries or connections depends on the specific scenario. 1. When it is necessary to filter data in advance, subqueries are more effective, such as finding today's order customers; 2. When merging large-scale data sets, the connection efficiency is higher, such as obtaining customers and their recent orders; 3. When writing highly readable logic, the subqueries structure is clearer, such as finding hot-selling products; 4. When performing updates or deleting operations that depend on related data, subqueries are the preferred solution, such as deleting users that have not been logged in for a long time.
Comparing Different SQL Dialects (e.g., MySQL, PostgreSQL, SQL Server)
Jul 07, 2025 am 02:02 AM
SQLdialectsdifferinsyntaxandfunctionality.1.StringconcatenationusesCONCAT()inMySQL,||orCONCAT()inPostgreSQL,and inSQLServer.2.NULLhandlingemploysIFNULL()inMySQL,ISNULL()inSQLServer,andCOALESCE()commonacrossall.3.Datefunctionsvary:NOW(),DATE_FORMAT()i
What is a composite primary key in SQL?
Jul 08, 2025 am 01:38 AM
AcompositeprimarykeyinSQLisaprimarykeycomposedoftwoormorecolumnsthattogetheruniquelyidentifyeachrow.1.Itisusedwhennosinglecolumncanensurerowuniqueness,suchasinastudent-courseenrollmenttablewherebothStudentIDandCourseIDarerequiredtoformauniquecombinat
How to find the second highest salary in SQL
Jul 14, 2025 am 02:06 AM
There are three core methods to find the second highest salary: 1. Use LIMIT and OFFSET to skip the maximum salary and get the maximum, which is suitable for small systems; 2. Exclude the maximum value through subqueries and then find MAX, which is highly compatible and suitable for complex queries; 3. Use DENSE_RANK or ROW_NUMBER window function to process parallel rankings, which is highly scalable. In addition, it is necessary to combine IFNULL or COALESCE to deal with the absence of a second-highest salary.
How to create empty tables with the same structure as another table?
Jul 11, 2025 am 01:51 AM
You can use SQL's CREATETABLE statement and SELECT clause to create a table with the same structure as another table. The specific steps are as follows: 1. Create an empty table using CREATETABLEnew_tableASSELECT*FROMexisting_tableWHERE1=0;. 2. Manually add indexes, foreign keys, triggers, etc. when necessary to ensure that the new table is intact and consistent with the original table structure.
