Printable Version of Topic

Posted by: shankar from vizag Apr 23 2022, 01:04 AM

Greetings to the community

Please guide me step by step procedure to auto generate a date column of mysql table with dates of the current year.

example

Date other_col1 other_col2
2022-04-23
2022-04-24
2022-04-25
...
...
...


Kindly guide me, I am using php mysql

regards

Posted by: CharlesEF Apr 23 2022, 05:07 AM

How about this?

Posted by: shankar from vizag Apr 24 2022, 12:35 AM

Thank you Charles

I tried your suggestion but the query returns nothing.

regards

Posted by: CharlesEF Apr 24 2022, 01:43 AM

Query? I posted a column entry for a table. You add a column with a datetime type and a default now(). Now every time a record is added the current date/time is inserted. I should say that now() (used as a function) is only supported in MySQL v8.0 and above. If now() doesn't work use CURRENT_TIMESTAMP instead. After you add records with the new column you can do queries. If you only want the date you can use date_format() to format the date.

Posted by: shankar from vizag Apr 25 2022, 08:22 PM

Thank you Charles

Yes, I used the column name in my create table query. As I am using mysql 5.0 (through PHPMYADMIN XAMPP SERVER 3.2.2), I tried with CURRENT_TIMESTAMP, still I did not succeeded.

However, the following code I got from the stackoverflow forum and it works,

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2022-01-01' and '2022-12-31'

The above select query showing the entire dates of the year in a column gen_date,

but I am not understanding how to save it to my table. I tried with following, but got multiple errors

create table tbl(
id INT NOT NULL AUTO_INCREMENT,

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2022-01-01' and '2022-12-31',
PRIMARY KEY ( tutorial_id )
);

Please guide me in this regard. I need a column with year full of dates.

regards

Posted by: shankar from vizag Apr 25 2022, 08:33 PM

Yes it works like following,

create table tbl(
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2022-01-01' and '2022-12-31'

);

Thank you so much Charles for your support.

Posted by: CharlesEF Apr 26 2022, 02:50 PM

Glad you got it working.