![]() ![]() VALUES ( DEFAULT, 1, 'We Have Always Lived in the Castle', now ( ) - interval '28 day', now ( ) ) I did this when creating the second example table: INSERT INTO book Within INSERT statements, you can combine interval and now() to generate timestamps at a specifically timed interval in the past or future. I don't have a use case for this yet, but I was impressed by it. The combination of accuracy (down the the microsecond) and natural language syntax is really powerful. So, for example, you can declare a very specific time interval like this: WHERE completed_at > now ( ) - interval '1 year 137 days 12 hours' Now, there's apparently a lot more that's possible with interval, most of which I obviously don't know. The available units for the interval data type are: microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, all of which can be abbreviated or written as plurals. My response to learning this was □! Yes, it merited my first use of an emoji in a sentence. The code now() - interval '30 day' automatically calculates the date 30 days in the past. Here's what it looks like: - Example 2Ĭompleted_at > now ( ) - interval '30 day' What I learned today was that this can also be written using an interval. So, to select data from within the past 30 days, I'd determine that 30 days ago was April 18, and the I'd write the query something like this: - Example 1Ĭompleted_at > ' 00:00:00' New Approach Using interval My typical approach for selecting data based on a time interval is first to calculate the specific date/time implied by the interval and then query based on whether records are from before or after that date. Previous Approach Using the Specific Date ![]() Here's a link to the docs, as well as the post I stumbled upon that demonstrated how to write this type of query. My searches lead me to learn about a new data type: interval. On a whim, I decided to see if PostgreSQL provided any clever ways to do this. While reviewing data from a logging table, I needed to select records from the past 30 days. Today, I learned about using interval to easily select a range of time. For instance, in India, the timezone difference with UTC is 5 hours 30 minutes.Thanks to my ignorance, PostgreSQL is an ongoing source of TILs. You can even make compound interval statements. To get only the date part, you can run the following command − SELECT (current_date + interval '3 days').date Because interval comparisons are made on timestamps. Notice how the output here also contains the time component. ![]() Output SELECT current_date + interval '3 days' You can also do these operations on date instead of timestamps SELECT current_date At the time of writing this, the output was − 10:57:13.28955+00 SELECT current_timestamp - interval '5 hours' Now, what if you want the relative time instead of the current time? For example, if you want the time corresponding to 5 hours prior to the current time, you can get it using intervals. The output will look like the following − 15:52:14.738867+00 You do that as follows − SELECT current_timestamp Quite often, you need the current timestamp in PostgreSQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |