Insert time in mysql using now()

Simple way to insert time information in mysql is using now()

now() will automatically insert appropriate depends on type, let say we have field in mysql database in these format :

1. birth (date), now() will insert current date, ex: 2007-05-02

2. birth (datetime), now() will insert current date + current time, ex : 2007-05-2 22:54:30

3. birth (time), now() will insert only time information, ex : 22:54:30

this snippet will get same result with now()

$birth=date(‘Y’).’-‘.date(‘m’).’-‘.date(‘d’); // will be same as 2007-05-02

example in mysql command line :

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>cd c:\appserv\mysql\bin

C:\AppServ\mysql\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.� Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-nt

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test;
Database changed

mysql> create table birthday_data(birth_date date not null, birth_time time not
null, full_birthday_time datetime not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc birthday_data;
+——————–+———-+——+—–+———————+——-+
| Field������������� | Type���� | Null | Key | Default������������ | Extra |
+——————–+———-+——+—–+———————+——-+
| birth_date�������� | date���� |����� |���� | 0000-00-00��������� |������ |
| birth_time�������� | time���� |����� |���� | 00:00:00����������� |������ |
| full_birthday_time | datetime |����� |���� | 0000-00-00 00:00:00 |������ |
+——————–+———-+——+—–+———————+——-+
3 rows in set (0.00 sec)

mysql> insert into birthday_data(birth_date) values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into birthday_data(birth_time) values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into birthday_data(full_birthday_time) values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into birthday_data(birth_date,birth_time,full_birthday_time) value
s(now(),now(),now());
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from birthday_data;
+————+————+———————+
| birth_date | birth_time | full_birthday_time� |
+————+————+———————+
| 2007-05-03 | 00:00:00�� | 0000-00-00 00:00:00 |
| 0000-00-00 | 09:32:15�� | 0000-00-00 00:00:00 |
| 0000-00-00 | 00:00:00�� | 2007-05-03 09:32:28 |
| 2007-05-03 | 09:32:55�� | 2007-05-03 09:32:55 |
+————+————+———————+
4 rows in set (0.00 sec)

mysql>

P.S :

  • Row 1,2,3 and 4 get data from single query
  • No limitation for function now() in query