What are the challenges you face when working across database platforms? Take the survey
Options

Generate Random Date in Python

andyevsandyevs Posts: 3
edited January 17, 2015 7:10AM in SQL Data Generator
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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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)
    
  • Options
    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
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Maybe you have set the source data column to allow NULL values?
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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)
    
  • Options
    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.
Sign In or Register to comment.