Menu
The title is pretty much explicit, my question is if i get two dates with hour:
Is it possible to pick a random datetime between these two datetime ?
I tried with the random() function but i don't really get how to use it with datetime
Thanks
Matthiew
203
In Postgresql, we can generate random dates between two dates with help of two functions random( ) and now( ). random( ) : It is the random function that returns a value between 0 (inclusive) and 1 (exclusive), so value >= 0 and value < 1.
In PostgreSQL, if you subtract one datetime value (TIMESTAMP, DATE or TIME data type) from another, you will get an INTERVAL value in the form ”ddd days hh:mi:ss”. So you can use DATE_PART function to extact the number of days, but it returns the number of full days between the dates.
PostgreSQL provides a datediff function to users. The datediff means we can return the difference between two dates based on their specified interval. The datediff function plays an important role in the database management system because datediff functions as a calendar and it is very helpful to users.
In PostgreSQL, the make_interval() function creates an interval from years, months, weeks, days, hours, minutes and seconds fields. You provide the years, months, weeks, days, hours, minutes and/or seconds fields, and it will return an interval in the interval data type.
You can do almost everything with the date/time operators:
select timestamp '2014-01-10 20:00:00' + random() * (timestamp '2014-01-20 20:00:00' - timestamp '2014-01-10 10:00:00') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
