Functions for retrieving current date/time
1. Functions for retrieving current date/time
Great work! Now we are going to learn how to retrieve and use the current date and time value in your queries at varying levels of precision.2. Retrieving the current timestamp
As you expand your SQL expertise, one of the most common techniques you'll use is to retrieve the current date and time value in your query. PostgreSQL provides several functions for doing this and we'll explore a few in this video beginning with the NOW() function. NOW() allows you to retrieve a timestamp value for the current date and time at the microsecond precision with time zone.3. Retrieving the current timestamp
Many times you will want to retrieve the current timestamp without the timezone. You can do this by explicitly casting it as seen in this example.4. Retrieving the current timestamp
Casting allows you to convert one data type to another such that columns stored in your database can be retrieved and output as a different type. There are a couple of ways you can cast data in your queries. We saw how to cast the timestamp returned by the NOW() function on the previous slide by adding two colons followed by the new type name. This syntax which uses the double colon operator is specific to PostgreSQL and non-conforming to the SQL standard. You can also use the CAST() function to achieve the same result by specifying the column name or in this case the NOW() function followed by the type name.5. Retrieving the current timestamp
PostgreSQL provides alternative methods for retrieving the current date and time values. The CURRENT_TIMESTAMP function returns the same result of the NOW() function as you see in this example and either approach can be used pretty much interchangeably in your queries.6. Retrieving the current timestamp
One difference between CURRENT_TIMESTAMP and NOW() is that with CURRENT_TIMESTAMP you can specify a precision parameter as you see in the example which will cause the result to have the seconds rounded to the number of fractional digits specified.7. Current date and time
Sometimes you want to get the current date or time but don't require the precision of a timestamp. In these instances you can use the CURRENT_DATE and/or CURRENT_TIME functions to achieve this. In this example, the CURRENT_DATE function will return a date value without the time.8. Current date and time
Additionally, you can use the CURRENT_TIME function to get the time value with timezone without the date value as seen in this example.9. Let's practice!
In the exercises, you'll get some experience on how to retrieve the current timestamp and use what you learned in the previous video to do some arithmetic operations with these results.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.