Generate Random Date in Python

The simplest way to populate your database with test data.

Generate Random Date in Python

Postby andyevs » Wed Mar 12, 2014 10:56 am

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
andyevs
 
Posts: 2
Joined: Wed Mar 12, 2014 10:50 am

Postby Brian Donahue » Thu Mar 13, 2014 4:26 pm

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.
Code: Select all
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)
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby andyevs » Fri Mar 14, 2014 11:00 am

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
andyevs
 
Posts: 2
Joined: Wed Mar 12, 2014 10:50 am

Postby Brian Donahue » Mon Mar 17, 2014 5:02 pm

Maybe you have set the source data column to allow NULL values?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Tue Mar 18, 2014 5:21 pm

Here are some changes that check for NULL data as well as ensuring the generated date is not after the maximum date.
Code: Select all
# 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)
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Data Generator 3

Who is online

Users browsing this forum: No registered users and 0 guests