search

Home  >  Q&A  >  body text

mysql - How to generate this sql table?

As shown in the figure, this sql table is to be generated from 5 o'clock to 23 o'clock, with an interval of 15 minutes.

How to generate it?

phpcn_u1582phpcn_u15822737 days ago572

reply all(2)I'll reply

  • 过去多啦不再A梦

    过去多啦不再A梦2017-05-27 17:41:01

    Using PHP

    $start = strtotime('20140227050000');
    $end = strtotime('20140227230000');
    $step = strtotime('1970-01-01 08:30:00');
    $data = array();
    while (($start+=$step) <= $end) {
        $data[] = array(
            's'=>date('Y-m-d H:i:s',($start-strtotime('1970-01-01 08:15:00'))),
            'e'=>date('Y-m-d H:i:s',$start)
        );
    }
    echo '<pre>';
    var_dump($data);

    Insert the data into the sql table

    Another MYSQL one

    -- 删除原有表
    DROP TABLE IF EXISTS `t`;
    -- 创建数据表
    CREATE TABLE IF NOT EXISTS `t` (
        `s` varchar(255),
        `e` varchar(255)
    );
    -- 创建存储
    create procedure protest()
    begin
    declare s int;
    declare t int;
    declare e int;
    set s=UNIX_TIMESTAMP('20140227050000');
    set t=900;
    set e=UNIX_TIMESTAMP('20140227230000');
    while s<e do
            set s=s+t;
        insert into t(`s`,`e`) values(FROM_UNIXTIME(s),FROM_UNIXTIME(s+t));
           set s=s+t;
    end while;
    end;
    -- 调用存储
    call protest();
    -- 删除存储
    drop procedure protest;

    reply
    0
  • 黄舟

    黄舟2017-05-27 17:41:01

    Do you want to generate such a table structure? Or is this table already existing? Do you want to insert table content like this

    reply
    0
  • Cancelreply