Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Randomizing the TIME but not DATE of a timestamp in Mysql

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?
1 Solution
select cast(cast(created-.5 as int)+rand() as datetime) from jos_content where created >= @date and created < @date+1
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.
Are you trying to update existing records or randomise the timestamp at the time the record is insrted?
MeridianManagementAuthor Commented:
I'm trying to fix existing records... I have no access to the original program so I can't change how it's displayed...
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now