Store time and date separably in a mysql database using default values

Question

In my mysql database table i have two columns called "date" and "time". "date" datatype is set to DATETIME and "time" datatype is set to TIMESTAMP. what i want to do is store only date in "date" column and store only time in "time" column. my table name is "loans".I'm not good in SQL querying. someone please show me exactly how to do that.

And also i have some another problems with time zones. i want to convert my database time format to UTC -5:00 (us time).how can i do that without change my PC time ?

Why ? if it's bad idea , how can i do this in mysql. should i change datatype ? — rafalefighter, Sep 10 '14 at 16:42
Just store the TIMESTAMP, or just store the DATETIME. Leave it at that. — Strawberry, Sep 10 '14 at 16:49
OK i can change that datatype, but when i tried to set defalut value to CURTIME() or CURRENT_DATE() it always giving me error.i set full permissions to my localhost and other users but i cant set default value to CURTIME() or CURRENT_DATE() — rafalefighter, Sep 10 '14 at 17:10
Either UTC_TIMESTAMP or NOW() depending on which one you want to store — Strawberry, Sep 10 '14 at 17:38
when i enter the NOW() command it store complete date and time both in one colum. i want to store date and time separate columns — rafalefighter, Sep 10 '14 at 18:25
what :S then can you please give me a good idea to do this :( — rafalefighter, Sep 10 '14 at 18:50
Didn't we cover this already? The data type should be TIMESTAMP or DATETIME. The value stored will either be the CURRENT_TIMESTAMP, NOW(), or the UTC_TIMESTAMP. All of this is covered in the manual. — Strawberry, Sep 11 '14 at 10:20

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

Separating DATE and TIME for same event is a bad idea. I would go for storing it in simple DATETIME field.

But you can do it with this

Give date field type DATE(not DATETIME) and time field type TIME (yes it exists and not TIMESTAMP)

Now this will allow you to store date in YYYY-MM-DD and time in HH:MM:SS

To set the time zone of your MYSQL server you can use this one

How do I set the time zone of MySQL?

edited May 23 '17 at 11:49

Community

1
1

answered Sep 10 '14 at 16:59

Sushant

1,354
1
14
31

Thank you for your reply. can you please shoe me what are the values to type Default field. I'm updating it using alter table option not query. can i put YYYY-MM-DD and HH:MM:SS to default filed ? – rafalefighter Sep 10 '14 at 17:44
it giving me the same error all the time 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 'HH:MM:SS AFTER `lid`' at line 2 SQL Statement: ALTER TABLE `waquis`.`loans` ADD COLUMN `time` TIME NULL DEFAULT HH:MM:SS AFTER `lid` – rafalefighter Sep 10 '14 at 17:45

Store time and date separably in a mysql database using default values

1 Answers1