how to create table with default date value as the current date in MySQL?

Question

Is there a way to create a table with a field that defaults to the current date value instead of a timestamp?

CREATE TABLE animal (
animal_id INT AUTO_INCREMENT PRIMARY KEY,
animal_name VARCHAR(50) NOT NULL,
animal_type VARCHAR(50) NOT NULL,
species VARCHAR(50) NOT NULL,
gender VARCHAR(10),
entry_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(10) DEFAULT 'present' NOT NULL
);

I have tried using CURRENT_DATE but i got this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_DATE, status VARCHAR(10) DEFAULT 'present' NOT NULL )' at line 7

Can you specify which version of Mysql you are using ?

Noman Shaikh
– Noman Shaikh

2024-04-19 11:33:32 +00:00
Commented Apr 19, 2024 at 11:33 — Noman Shaikh
– Noman Shaikh, Commented Apr 19, 2024 at 11:33

ysth · Accepted Answer · 2024-04-19 12:35:33Z

2

Current versions of mysql allow arbitrary self-contained expressions but you must enclose them in parenthesis:

DEFAULT (CURRENT_DATE)

That said, CURRENT_DATE is the date in the timezone of the current session, so different sessions may use different defaults. I recommend never using any of the functions that depend on the session timezone (now, current_date, etc). If you want the field to default to the currrent date in some specific timezone, be explicit about it, in a way that can't vary by session. E.g. for US Pacific time:

DEFAULT (date(convert_tz(utc_timestamp,'+00:00','America/Los_Angeles')))

edited Apr 19, 2024 at 12:35

answered Apr 19, 2024 at 12:16

ysth

99.1k6 gold badges126 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thallius Over a year ago

And I would recommend never ever store datetimes other than in UTC. Its part of the application to determine the current timezone of the user and show the local time properly

ysth Over a year ago

@Thallius agreed. but some times it is convenient to store a date (not a datetime)

Collectives™ on Stack Overflow

how to create table with default date value as the current date in MySQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related