Assign DATETIME a value on INSERT to DB based on BETWEEN function values

Question

I'd like to, when inserting a new record to TABLE 1, assign DATETIME a value based on another tables values, but having trouble putting it all together.

TABLE1 has interval_id and submit_date columns (and many others)

TABLE2 has interval_id, begin_time, and end_time

Example:

INSERT statement with DATETIME of 2012-10-24 07:29:41
Peel off just the TIME
Reference the interval_id's in both tables
Look up where TIME falls between T2.begin_time and T2.end_time (06:00:00 and 07:59:59 in this case)
once determined,assign the corresponding value to it in T1.interval_id

So far:

INSERT INTO `TABLE1` (null, now())
WHERE
  DATE(submit_date) = curdate()
  AND TIME(submit_date)

----not sure on coding here to compare TIME to the begin and end time column data----

full insert statement:

    $sql        = "INSERT INTO `summary` VALUES (null,'$user_id','$task_id',NOW(),'$tcompleted_id','$minutes_id','$hourinterval_id')
                   SELECT NOW(), hourinterval_id 
                   FROM `hour_interval` H
                   WHERE TIME(NOW()) BETWEEN H.start_hour AND H.end_hour LIMIT 1"

An INSERT statement is for new rows only, and therefore has no WHERE clause. Do you mean to update existing rows? — Michael Berkowski
– Michael Berkowski, Commented Oct 26, 2012 at 3:25
@MichaelBerkowski The syntax is incorrect, but you can INSERT values based on the result of a SELECT statement which would support a WHERE clause. — doublesharp
– doublesharp, Commented Oct 26, 2012 at 3:31

doublesharp · Accepted Answer · 2012-10-26 03:28:29Z

1

You can insert the results of a SELECT statement:

INSERT INTO `TABLE1`(curdate, interval_id)
SELECT now(), inteval_id FROM `TABLE2` t2 
WHERE TIME(now()) BETWEEN t2.start_time AND t2.end_time LIMIT 1

answered Oct 26, 2012 at 3:28

doublesharp

27.9k6 gold badges56 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Mike Over a year ago

The select statement works, but I keep getting a syntax error? I'll post my full insert above.

Mike Over a year ago

Full error: 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 'SELECT NOW(), hourinterval_id FROM hour_interval H WHERE TI' at line 2

doublesharp Over a year ago

What is your full sql statement?

Anthony Hatzopoulos Over a year ago

hour_interval H is the same as hour_interval AS H and doublesharp used t2 not H.

ypercubeᵀᴹ Over a year ago

INSERT INTO table1 SELECT NULL, '$user_id', '$task_id', NOW(), '$tcompleted_id', '$minutes_id', hourinterval_id FROM table2 WHERE ...

|

Collectives™ on Stack Overflow

Assign DATETIME a value on INSERT to DB based on BETWEEN function values

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related