The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> AUTO GENERATE -DATE- COLUMN OF MYSQL TABLE WITH ONE YEAR FULL DATES
shankar from vizag
post 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

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Apr 23 2022, 05:07 AM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



How about this?
CODE
ColumnName datetime default now()
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Apr 24 2022, 12:35 AM
Post #3


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Apr 23 2022, 03:37 PM) *

How about this?
CODE
ColumnName datetime default now()




Thank you Charles

I tried your suggestion but the query returns nothing.

regards
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Apr 24 2022, 01:43 AM
Post #4


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(shankar from vizag @ Apr 24 2022, 12:35 AM) *
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Apr 25 2022, 08:22 PM
Post #5


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Apr 24 2022, 12:13 PM) *

QUOTE(shankar from vizag @ Apr 24 2022, 12:35 AM) *
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post 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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Apr 26 2022, 02:50 PM
Post #7


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Glad you got it working.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 29th March 2024 - 08:46 AM