CODEWITHSUNDEEP
mysqlsql
Speedychuck
Speedychuck

Reputation: 400

INSERT EPOCH AND Unix TimeStamp between 2 dates

I have an small issue, I have a insert SQL statement which is like follows:

INSERT INTO table (`col`, `col`, `col`)
    VALUES '1', '2', FLOOR(950000+RAND()*(550000-950000));

One of the columns is a EPOCH Unix TImeStamp, I want to be able to RANDOMIZE INSERTS between 2 dates: so for example:

RANDOMIZED INSERT VALUE BETWEEN: 1469696000 and 1479996000

how can I achieve this using RAND but set the condition so it must be between those values so when its converter its between those EPOCH dates?

Upvotes: 1

Views: 565

Answers (1)

1000111
1000111

Reputation: 13519

You can give it a try:

INSERT INTO table (`col`, `col`, `col`)
    VALUES '1', '2', (FLOOR( 1469696000 + RAND( ) *(1479996000-1469696000 )))

Note:

(FLOOR( A + RAND( ) * B)) will return a random number between A and A+B (inclusive)

EDIT:

In order to get Random unix timestamp between now and end of this year:

(FLOOR( UNIX_TIMESTAMP() + RAND( ) *(UNIX_TIMESTAMP((CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) DAY) + INTERVAL 1 YEAR) - UNIX_TIMESTAMP())))

So here's the full query:

INSERT INTO TABLE (
    `col`,
    `col`,
    `col`
)
VALUES
    '1',
    '2',
    (
        FLOOR(
            UNIX_TIMESTAMP() + RAND() * (
                UNIX_TIMESTAMP(
                    (
                        CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) DAY
                    ) + INTERVAL 1 YEAR
                ) - UNIX_TIMESTAMP()
            )
        )
    )

Upvotes: 1

Related Questions