Red Gate forums :: View topic - Generate Random Date in Python
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 3
SQL Data Generator 3 forum

Generate Random Date in Python

Search in SQL Data Generator 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
andyevs



Joined: 12 Mar 2014
Posts: 2

PostPosted: Wed Mar 12, 2014 10:56 am    Post subject: Generate Random Date in Python Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Thu Mar 13, 2014 4:26 pm    Post subject: Reply with quote

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:
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)
Back to top
View user's profile Send private message
andyevs



Joined: 12 Mar 2014
Posts: 2

PostPosted: Fri Mar 14, 2014 11:00 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Mon Mar 17, 2014 5:02 pm    Post subject: Reply with quote

Maybe you have set the source data column to allow NULL values?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Tue Mar 18, 2014 5:21 pm    Post subject: Reply with quote

Here are some changes that check for NULL data as well as ensuring the generated date is not after the maximum date.
Code:
# 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)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group