SQL结构和日历？

Question

I'm having trouble unpacking a calendar structure from sql into golang structs, here is what I have.

type year struct {
year int
months []month
}

type month struct {
month int
days []day
}

type day struct {
day int
hours map[int]bool
}

I'm planning an appointment calendar, each day may have 10:00, 11:00, 12:00, 13:00 etc. and I will read out a maximum of 3 months at a time. I cannot figure out how to unpack the following schema:

CREATE TABLE appointments (
id INT,
year INT,
month INT,
day INT,
hour INT,
teacher INT, (id of teacher)
student INT, (id of student)
amount DECIMAL, (amount charged for the appointment)
booked BOOL, (records availability)
)

The query:

SELECT year, month, day, hour, booked FROM appointments a WHERE a.teacher = ? ORDERY BY day, month, year;

Answer 1

In SQL Server you can create date tables and date time interval tables too easily.

You can check how I implemented time interval table in SQL Server in referenced document.

I joined a numbers table with 24 rows matching to hours and a second numbers table reference with 365 rows matching to dates in a year

You need to convert below T-SQL to use it in your mySQL statements

declare @date datetime = '20100101'

SELECT
 dt.number+1 DayNo,
 tt.number+1 HourNo,
 dateadd(dd,dt.number,@date) [date],
 dateadd(hh,tt.number,dateadd(dd,dt.number,@date)) [start],
 dateadd(hh,tt.number+1,dateadd(dd,dt.number,@date)) [end]
FROM master..spt_values dt, master..spt_values tt
WHERE
 dt.Type = 'P' AND
 tt.Type = 'P' AND
 dt.number < 365 AND
 tt.number < 24 AND
 dateadd(dd,dt.number,@date) < dateadd(yy,1,@date)
ORDER BY dt.Number, tt.Number