Generate Random Date in Python

andyevs

Hi,

I'm having real trouble using Python Script to generate a random date between two dates. Ideally I'd like to call something like the following:

GenerateDate(DateOfBirth,"01/03/2014")

and for that to return be a random date between the DateOfBirth column and 1st March 2014.

Does anyone have any examples of this?

Many thanks,

Andrew

Brian Donahue

There was no example, so I wrote one. This takes the value of the generated "DateOfBirth"column and adds a random number of seconds to it, but not so many that the date exceeds March 1, 2014.

import time
from datetime import datetime
from time import mktime
from datetime import timedelta
import random

def main(config):
    # How many seconds between [date] and [01/03/2014]? That will be maxSeconds
    refDate=datetime.strptime(DateOfBirth.ToString(), "%d/%m/%Y %H:%M:%S")
    # In above, be careful that your locale is returning dates in a matching format
    maxDate=datetime.strptime("01/03/2014", "%d/%m/%Y")
    # Calculate the difference between the starting date and the maximum date
    timeDifference=maxDate-refDate
    maxSeconds=timeDifference.total_seconds()
    # Choose a random number between 1 and secondsMax
    randSeconds=random.randrange(1,maxSeconds)
    # Add the random number of seconds to the original date
    retDate=refDate+timedelta(seconds=randSeconds)
    return str(retDate)

andyevs

Hi Brian,

Thanks for this. Just what I needed. Although I am having an issue when generating at scale. I have created a SQL table with two datetime columns, DateOfBirth and DateOfEvent. I am generating a random date into DateOfBirth using the standard SQL datetime generator and then I have implemented your script to generate values for DateOfEvent.

This works fine when generating 1,000 records but when generating 1,000,000 records I get the generation error below:

[dbo].[zzRedGateTest]
The value '' cannot be inserted into column DateOfEvent
RedGate.SQLDataGenerator.Engine.DataGeneration.InvalidColumnDataException: The value '' cannot be inserted
into column DateOfEvent at œœœœ.œœœœ.œœœœ() at œœœœ.œœœœ.Read() at
RedGate.SQLDataGenerator.Engine.DataGeneration.TypeTranslationDataReader.Read() at
System.Data.SqlClient.SqlBulkCopy.ReadFromRowSource() at
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at
System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at œœœœ.œœœœ.œœœœ(CancellableController œœœœInt32 œœœœ, ConnectionProperties œœœœ, GenerateAction œœœœ, SqlConnection œœœ, SDGProject œœœœ, GenerationReport Inserted 0 rows
Generation started at 14 March 2014 09:44:48, taken: 00:01:18 (hh:mm:ss)

I can't see how empty strings would be inserted into the value. Could you please let me know how to resolve this issue?

Many thanks,

Andrew

Brian Donahue

Maybe you have set the source data column to allow NULL values?

Brian Donahue

Here are some changes that check for NULL data as well as ensuring the generated date is not after the maximum date.

# Basic generator template
import time
from datetime import datetime
from time import mktime
from datetime import timedelta
import random

def main(config):
    fmtString="%d/%m/%Y %H:%M:%S"
    defaultDate=datetime.strptime("01/02/2014", "%d/%m/%Y")
    # How many seconds between [date] and [01/03/2014]? That will be maxSeconds
    if DateOfBirth.IsNull:
        return defaultDate.strftime(fmtString)
    refDate=datetime.strptime(DateOfBirth.ToString(), fmtString)
    # In above, be careful that your locale is returning dates in a matching format
    maxDate=datetime.strptime("01/03/2014", "%d/%m/%Y")
    # Calculate the difference between the starting date and the maximum date
    timeDifference=maxDate-refDate
    maxSeconds=timeDifference.total_seconds()
    if maxSeconds < 1:
        maxSeconds=1
    # Choose a random number between 1 and secondsMax
    randSeconds=random.randrange(1,maxSeconds)
    # Add the random number of seconds to the original date
    retDate=refDate+timedelta(seconds=randSeconds)
    return retDate.strftime(fmtString)

Kahanho1

This happens to be when the database I am running the queries against isn't available. In our case the server that we query against is only available via VPN when we travel. If we don't sign into the VPN first the project will just hang at "reading permissions" while loading the project.

I would have expected some sort of timeout when opening a project if a server cannot be reached, but it just seems to hang forever. As mentioned you have to go into task manager to kill the process, hitting "cancel" doesn't do anything.