如何在指定的日期范围内在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个成员的健身房站点做演示，但是添加的成员值都具有相同的开始日期和时间.尝试将它们分成数据库中不同的月度注册，例如，八月有50位用户在不同的日期和时间注册，三月有35位用户在10月注册，等等.希望现在变得更加清楚.谢谢–

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屋！