如何在指定的日期范围内在 SQL 中更新/插入随机日期 [英] How to update/Insert random dates in SQL within a specified Date Range

问题描述

请原谅我.我是一个绝对的新手，我需要帮助处理 phpmyadmin 中的这个表

Please forgive me. I am an absolute newbie and I need help with this table in phpmyadmin

我的表有以下列:

Primary_ID, Begin_Date, End_Date, Timestamp

如何在 phpmyadmin 中更新选定的行，在指定的日期范围内(例如:一个月 30 天)随机生成 begin_dates 和时间戳.例如期望的结果

How do I update in phpmyadmin, selected rows with randomly generated begin_dates and timestamp within a specified date range (eg: 30 days in a month). E.g of desired outcome

Primary_id--- Begin_Date -------------Timestamp

1.------------2008-09-02--------------2008-09-02 21:48:09

2.------------2008-09-03--------------2008-09-03 15:19:01

3.------------2008-09-14--------------2008-09-14 01:23:12

4.------------2008-09-27--------------2008-09-27 19:03:59

日期范围在 2008-09-01 和 2008-09-31 之间.时间 24 小时可变

Date Range between 2008-09-01 and 2008-09-31. Time is variable 24 hrs

我是新手，所以在 phpmyadmin 中可以使用的语法会有很大帮助.

I am a newbie, so a syntax that will work in phpmyadmin will help greatly.

我们正在为一个拥有 500 名会员的健身房网站制作演示文稿，但添加的会员值都具有相同的开始日期和时间.尝试将它们在数据库中分成不同的每月注册，例如8 月不同日期和时间注册的50 人，10 月35 人等.希望现在更清楚.谢谢——

We are making a presentation for a gym site with 500 members but the added member values all have the same begin date and time. Trying to separate them into different monthly registrations in the database, eg 50 people registered in August at different days and times, 35 people in October, etc. Hope it is clearer now. Thanks –

当我尝试以下答案之一时，出现此错误:#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的$randomDate = rand(1,31)"附近使用的正确语法.因此，理想情况下，我可以将代码复制并粘贴到 phpmyadmin 中并进行最少的编辑，我将不胜感激.如果可能，按顺序排列.让一个完全的假人理解和执行.

When I try one of the below answers, I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$randomDate = rand(1,31)' at line 1. So ideally, a code I can copy and paste into phpmyadmin with minimal editing will be appreciated. In sequence if possible. For a total dummy to understand and execute.

推荐答案

我会从这样的事情开始.可以将其中的一堆组合在一起，但我将其拆分以便您可以看到我在做什么.

I'd start with something like this. A bunch of these can be combined, but I split it up so you can see what I'm doing.

要获得随机数，您可以使用 rand().获取日期，小时，分钟和秒

To get random numbers, you can use rand(). Get one for the date, hour, minute, and second

$randomDate = rand(1,31);
$randomHour = rand(1,24);
$randomMinute = rand(0,59);
$randomSecond = rand(0,59);

您需要前导零(03 而不是 3)，以便您可以在需要时使用 str_pad 添加它们

You will want leading zeros (03 instead of 3) so you can use str_pad to add them, if required

$randomDate = str_pad($randomDate, 2, '0',STR_PAD_LEFT);
//The '2' is how many characters you want total
//The '0' is what will be added to the left if the value is short a character

对所有其他随机值执行相同操作.正因为我喜欢简洁的查询，所以接下来你应该组成你的最终更新字符串.

Do the same with all your other random values. Just because I like neat queries, you should make up your final update strings next.

$newDate = '2008-09-'.$randomDate;
$newTime = $randomHour.':'.$randomMinute.':'.$randomSecond;

现在我不知道您如何确定要更新哪些行，所以我将把它留给您.例如，如果您想使用 Primary_id 3 执行此操作，我将向您展示一个查询:

Now I don't know how you're determining which rows you want to update, so I will leave that up to you. For an example, I will show you a query if you wanted to do this with Primary_id 3:

$x = mysql_query("UPDATE yourTable SET Begin_Date="$newDate", Timestamp="$newTime" WHERE Primary_id = 3");

这篇关于如何在指定的日期范围内在 SQL 中更新/插入随机日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！