AUTO GENERATE -DATE- COLUMN OF MYSQL TABLE WITH ONE YEAR FULL DATES |
AUTO GENERATE -DATE- COLUMN OF MYSQL TABLE WITH ONE YEAR FULL DATES |
shankar from vizag |
Apr 23 2022, 01:04 AM
Post
#1
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
CharlesEF |
Apr 23 2022, 05:07 AM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
How about this?
CODE ColumnName datetime default now() |
shankar from vizag |
Apr 24 2022, 12:35 AM
Post
#3
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
|
CharlesEF |
Apr 24 2022, 01:43 AM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I tried your suggestion but the query returns nothing. 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. |
shankar from vizag |
Apr 25 2022, 08:22 PM
Post
#5
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
I tried your suggestion but the query returns nothing. 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.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 |
shankar from vizag |
Apr 25 2022, 08:33 PM
Post
#6
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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. |
CharlesEF |
Apr 26 2022, 02:50 PM
Post
#7
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Glad you got it working.
|
Lo-Fi Version | Time is now: 26th September 2024 - 10:39 AM |