Welcome to srcodigofuente.com! In this tutorial I will teach you everything you need to know about the SQL NOW function.
This function is very useful for working with dates and times in databases and is a must in any project that involves transactions or date management.
Content
- Definition and syntax
- Examples of using the NOW() function in SELECT queries
- Example 1: Get the current date and time
- Example 2: Get the records created in the last 24 hours
- Example 3: Update the modification date of a record
Definition and syntax
The NOW() function in SQL is used to get the current date and time of the system on which the query is being executed.
It is important to mention that this function varies depending on the database engine used. In MySQL, for example, NOW() is equivalent to CURRENT_TIMESTAMP().
In Oracle, the equivalent function is SYSDATE.
The syntax of the NOW() function is very simple:
NOW()
This function does not require any parameters and is used directly in SQL queries.
If you want to know more about the NOW() function in different database engines, you can consult the following official definitions:
- Official definition of NOW() in MySQL
- Official definition of CURRENT_TIMESTAMP() in MySQL
- Official definition of SYSDATE in Oracle
Examples of using the NOW() function in SELECT queries
Example 1: Get the current date and time
SELECT NOW();
Result:
NOW() |
---|
2023-02-24 16:30:45 |
Example 2: Get the records created in the last 24 hours
Suppose we have a table of products in an online store and we want to get the products that have been added in the last 24 hours.
We can use the NOW() function together with the DATE_SUB() function to get the date and time of 24 hours ago and compare it with the date the records were created.
SELECT * FROM productos WHERE fecha_creacion >= DATE_SUB(NOW(), INTERVAL 1 DAY);
Result:
id | name | price | Creation date |
---|---|---|---|
1 | Red shirt | 19.99 | 2023-02-23 15:30:45 |
3 | Blue sweatshirt | 49.99 | 2023-02-24 10:45:32 |
4 | Black pants | 29.99 | 2023-02-24 12:30:15 |
Example 3: Update the modification date of a record
Suppose we want to update the modification date of a record in our products table.
We can use the NOW() function in the UPDATE query to insert the current date and time.
UPDATE productos SET fecha_modificacion = NOW() WHERE id = 1;
Result:
id | name | price | Creation date | modification date |
---|---|---|---|---|
1 | Red shirt | 19.99 | 2023-02-23 15:30:45 | 2023-02-24 16:30:45 |
2 | Black jacket | 89.99 | 2023-02-21 12:15:20 | 2023-02-21 09:30:00 |
3 | Blue sweatshirt | 49.99 | 2023-02-24 10:45:32 | 2023-02-24 10:45:32 |
In addition to the NOW function, there are many other useful functions for working with dates in SQL. Here are some examples of what can be done:
- DATEADD: Allows you to add or subtract a time interval to a given date.
- DATEDIFF: Calculates the difference between two dates.
- DATEPART: Returns a specific part of a date, such as the day, month, or year.
- GETDATE: Returns the current date and time.
- DATEFORMAT: Formats a date according to a specified pattern.
These functions can be very useful for working with dates in SQL, either for filtering data or for performing calculations.
If you want to learn more about how to work with dates in SQL, we recommend that you investigate more about these functions and try them in your own projects.
We hope you found this tutorial on the SQL NOW function useful and help you understand how it works.
Remember that the best way to learn is by practicing, so feel free to try the examples we have shown here and experiment with your own queries.
Good luck!