Connecting date and time tables

I have 2 tables,the one stores dates in this format 2015-11-15 and the other stores times in this format 10:00:00.

How I could connect them such that for every date in the dates table times spanning from 10:00 to 23:00 in the times table are associated with.

I mean do I have to add extra columns in one or both of the tables…

Clearly it is a one-to-many relationship

And another thing…as I see it times in the times table must be repeated many times…for each date there is…so as to indicate the connection

What do you think?

if this is mysql, please do a SHOW CREATE TABLE for each table

then please show some sample rows in each table

also, i can’t figure out what you’re askin

show create for date:
CREATE TABLE date( date date NOT NULL, PRIMARY KEY (date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

show create for time:

CREATE TABLE time( time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The date table has dates for the next 7 months…and the times table contains times from 10:00 to 22:00(one hour interval)`…I am building an appointments app and I want to combine the two tables to present the user with the available appointment slots…

The way things are now I cannot…
For every date a time span must correspond between 10:00 and 23;00.

So what I must do…what foreign keys to create…do I need to add extra columns to the table/tables.
What kind of relationship must exist between the two tables…one to many?

Questions that need answer.

is that all they have? just one column per table?

you’re looking for CROSS JOIN !! :slight_smile:

will that serve my purpose?

If cross Join what I have to make I will do it…

SELECT `date`.`date`
     , `time`.`time`
  FROM `date`
CROSS
  JOIN `time`

i would strongly advise you to use different names for your tables and columns, because DATE and TIME are reserved words

1 Like

I used this statement:

`select * from date,time;`

it did the job…what more your code has to offer.

Either way both statements do NOT create a separate table…the aim here is that the result of the join goes into a separate(3rd) table…how am I going to achieve that?

[quote=“designtrooper, post:7, topic:207160, full:true”]…the aim here is that the result of the join goes into a separate(3rd) table…how am I going to achieve that?
[/quote]

use this syntax (look it up in the manual if you’re not sure) –

CREATE TABLE newtable ( columns ) SELECT columns FROM ...

thanks…

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.