[Resolved] Dates & Times

[rcoursey]

need to do two things with the dates in a MySQL DB.

1. A place to store things like first login date, last login date, birthdate, etc where I am only looking at a month:day:year (no time component)

2. A place to not only store dates but times also. These would control cutoff views based on the date & time. THese fields will also use date addition to calculate the cutoff time from the current time.

Which date types are best for each?

How do I get the date or date/time for this moment entered into the DB?

[rcoursey]

A little more background:

$signup_date = date('Y-m-d');
$active = intval($active);
$result = dbquery("INSERT INTO users (userid, membertype, refercode, uname, password, email_address, first_name, last_name, sex, address, city, state, zip, country, birthdate, active, signup_date) VALUES (NULL, '$membertype', '$refercode', '$uname', password('$password'), '$email_address', '$first_name', '$last_name', '$sex', '$address', '$city', '$state', '$zip', '$country', '$birthdate', '$active', '$signup_date')");
if ($result)

the dbquery() just connects and passes the SQL to the query.

signup_date is of type 'date' in the table definition.

When I run the query I get this error message:

add is Mode. Time User Nameadd is Mode in if 1. Time User Name in if 1add is Mode. Time User NameQuery failed - INSERT INTO users (userid, membertype, refercode, uname, password, email_address, first_name, last_name, sex, address, city, state, zip, country, birthdate, active, signup_date) VALUES (NULL, '1', '55555', 'Time', password('time'), '[e-mail]', 'Time', 'Again', 'M', '4 time st', 'here', 'AK', '66552', 'US', '1', '1', '2003-06-03')

[tsinka]

Hi,

change your dbquery so that it prints the exact error message by using mysql_error() after the query.

[rcoursey]

Here is the error code:

Query failed - INSERT INTO users (userid, membertype, refercode, uname, password, email_address, first_name, last_name, sex, address, city, state, zip, country, birthdate, active, signup_date) VALUES (NULL, '1', '55555', 'Time', password('time'), '[e-mail]', 'Time', 'Again', 'M', '4 time st', 'here', 'AK', '66552', 'US', '', '1', '2003-06-03')Duplicate entry 'Time' for key 2

[tsinka]

Hi,

seems like you have a unique key on the uname field. Most likely there already exists a row with a uname of 'Time'. Try to use another user name or change your code so that it first checks if the user already exists.

Thomas

[rcoursey]

You are correct! I was trying to add the user name twice. The record is being entered.

Dates are still confusing to me.

[tsinka]

You can use the datetime type for the date information. To insert the actual date and time you can then use the now() function of MySQL in your insert query.

INSERT INTO thetable (datetimefield) VALUES (now())

To get Y-m-d you can use

SELECT DATE_FORMAT(datetimefield,'%Y-%m-%d') AS dt FROM ...

The time:

SELECT DATE_FORMAT(datetimefield,'%H:%i:%s') AS tm FROM ...

Or you could use UNIX_TIMESTAMP to get a timestamp usable with the php date() function:

SELECT UNIX_TIMESTAMP(datetimefield) AS ts FROM ...

// fetch the records with e.g. mysql_fetch_array

// do something
echo "The date: ".date("Y-m-d",$row['ts'])."<br>\n";

There are more ways ... I'd suggest to read the MySQL and/or PHP manuals about date and time functions 🙂