Randomizing the TIME but not DATE of a timestamp in Mysql

Posted on 2007-08-02
Last Modified: 2012-06-22
I have a MySQL database let's say the table is called jos_content and the timestamp is called "created" of the format: "2006-11-28 06:56:00". How do you randomize the hours and minutes and seconds without affecting the date?

Is it also possible to add a where clause so that I can randomize only the timestamps in the database of a specific day?
Question by:MeridianManagement
    LVL 15

    Expert Comment

    select cast(cast(created-.5 as int)+rand() as datetime) from jos_content where created >= @date and created < @date+1
    LVL 24

    Expert Comment

    A random timestamp? I am trying to imagine a purpose for such a construct, but coming up with a blank. If you are just trying to order events within a day in a random manner, you can use one of the following methods:

      Use "ORDER BY RAND()" when you select the entries.
      Create an 'order_by' column, and shuffle the values in that column. Then when you select your data, use "ORDER BY `order_by`".

    I don't know of a MySQL way to randomize just the time portion of a timestamp (or for that matter, the date). Timestamps have a very specific purpose, and randomization doesn't fit well with that purpose. You can script such randomness in the language of your choice, if absolutely necessary.
    LVL 4

    Expert Comment

    Are you trying to update existing records or randomise the timestamp at the time the record is insrted?
    LVL 2

    Author Comment

    I'm trying to fix existing records... I have no access to the original program so I can't change how it's displayed...
    LVL 4

    Accepted Solution

    okay well

    update jos_content set created = date_add(created, interval rand()*6 hour)

    Will add a random amount of time between 0 and 6 hours. You can also use date_sub instead of date_add to remove time.
    i would suggest using both with a where clause to adjust which rows they affect to add to the randomising.

    This function may though flick the day over or under the current day so you could use this to stop the update if that would happen

    update jos_content set created = date_add(created, interval rand()*6 hour) where dayofyear(date_add(created, interval rand()*6 hour)) = dayofyear(created)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now