MySQL concatenation with curdate curtime?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

MySQL concatenation with curdate curtime?

Post by JAB Creations »

I'm not sure how to use concatenation in MySQL. I've tried the period and the plus symbol and neither seem to work. Below does not generate any error messages however it does not insert anything useful either...?

Code: Select all

$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CURDATE() + CURTIME())";
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL concatenation with curdate curtime?

Post by jaoudestudios »

$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CURDATE() + CURTIME())";
Why not store the date with a php unix timestamp, then from that you can get any time and/or date format you want.

Instead of CURDATE() + CURTIME() just use time()

Resulting in...

Code: Select all

$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', ".time().")";
Then when you want to display the result in your php page just use...

Code: Select all

date("d m Y" , *UNIX TIMESTAMP FROM DB*)
You can change d m Y to any format you want, including time. The codes for all the options can be found on php.net
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenation with curdate curtime?

Post by VladSun »

I vote for using timestamps too, but formating it at DB layer.
If you don't want to use timestamps - http://dev.mysql.com/doc/refman/5.0/en/ ... nction_now
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL concatenation with curdate curtime?

Post by JAB Creations »

I decided this would be a better format since essentially the MySQL documentation severely lacks any useful examples, tutorial sites are doing mostly useless/static crap, and I can later use PHP's explode function if I need to "clean" the format up.

Code: Select all

$date_time = date("Y:m:d:H:i:s");
$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', '$date_time')";
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL concatenation with curdate curtime?

Post by JAB Creations »

Hi Vlad, I tried that stuff out and was never able to merge the date and time together in to a single row/column.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenation with curdate curtime?

Post by VladSun »

JAB Creations wrote:... since essentially the MySQL documentation severely lacks any useful examples, tutorial sites are doing mostly useless/static crap ...


???

There are tons of them in the official manual
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenation with curdate curtime?

Post by VladSun »

JAB Creations wrote:Hi Vlad, I tried that stuff out and was never able to merge the date and time together in to a single row/column.
I don't believe it - give an example.
mysql> SELECT NOW();
-> '2007-12-15 23:50:26'
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL concatenation with curdate curtime?

Post by JAB Creations »

Maybe it was the initial column width? I'm not sure...now works now though. :mrgreen: Thanks!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL concatenation with curdate curtime?

Post by Benjamin »

Here are two ways to do it.

Code: Select all

 
INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CONCAT(CURDATE(), ' ', CURTIME()))
INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', NOW())
 
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL concatenation with curdate curtime?

Post by jaoudestudios »

Instead of using the php unix timestamp
time()
sql has its own one...

Code: Select all

UNIX_TIMESTAMP()
Converting from unix timestamp in php is easy and well documented, but there is a similar way in sql

Code: Select all

FROM_UNIXTIME(unix timestamp, format)
Example...

Code: Select all

FROM_UNIXTIME(unix timestamp, '%d %m %Y')
Post Reply