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: 6590
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: 6590
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: 6590
Joined: Mon Aug 23, 2004 10:48 am

Re: Generate Random Date in Python

Postby Kahanho1 » Sat Jan 17, 2015 1:10 pm

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.
lab gaya
Kahanho1
 
Posts: 1
Joined: Sat Jan 17, 2015 12:46 pm


Return to SQL Data Generator 3

Who is online

Users browsing this forum: No registered users and 0 guests

cron