[Resolved] Date/Time help

[wanted420]

Ok i know there is an easier way to do this but i have never been able to get date/time down and working right. It was a date/time type and i tried to use now() but it didnt work. (got that idea from a tutorial). This is how i ended up getting it to work. Here is my database setup for the date/time:

signup_date varchar(45) NOT NULL default '00-00-0000 00:00:00',

Here is how i submit it. It is way to long. It wouldnt be this bad but it drops the ZERO for the minutes and seconds and its on a 24 hour clock and i want a 12 hour clock:

$today = getdate(); 
switch($today[hours])
{
case 1:
$hours = 1;
$AMPM = AM;
break;
case 2:
$hours = 2;
$AMPM = AM;
break;
case 3:
$hours = 3;
$AMPM = AM;
break;
case 4:
$hours = 4;
$AMPM = AM;
break;
case 5:
$hours = 5;
$AMPM = AM;
break;
case 6:
$hours = 6;
$AMPM = AM;
break;
case 7:
$hours = 7;
$AMPM = AM;
break;
case 8:
$hours = 8;
$AMPM = AM;
break;
case 9:
$hours = 9;
$AMPM = AM;
break;
case 10:
$hours = 10;
$AMPM = AM;
break;
case 11:
$hours = 11;
$AMPM = AM;
break;
case 12:
$hours = 12;
$AMPM = PM;
break;
case 13:
$hours = 1;
$AMPM = PM;
break;
case 14:
$hours = 2;
$AMPM = PM;
break;
case 15:
$hours = 3;
$AMPM = PM;
break;
case 16:
$hours = 4;
$AMPM = PM;
break;
case 17:
$hours = 5;
$AMPM = PM;
break;
case 18:
$hours = 6;
$AMPM = PM;
break;
case 19:
$hours = 7;
$AMPM = PM;
break;
case 20:
$hours = 8;
$AMPM = PM;
break;
case 21:
$hours = 9;
$AMPM = PM;
break;
case 22:
$hours = 10;
$AMPM = PM;
break;
case 23:
$hours = 11;
$AMPM = PM;
break;
case 0:
$hours = 12;
$AMPM = AM;
break;

default:
break;
}

switch($today[minutes])
{
case 0:
$minutes = '00';
break;
case 1:
$minutes = '01';
break;
case 2:
$minutes = '02';
break;
case 3:
$minutes = '03';
break;
case 4:
$minutes = '04';
break;
case 5:
$minutes = '05';
break;
case 6:
$minutes = '06';
break;
case 7:
$minutes = '07';
break;
case 8:
$minutes = '08';
break;
case 9:
$minutes = '09';
break;
}

switch($today[seconds])
{
case 0:
$seconds = '00';
break;
case 1:
$seconds = '01';
break;
case 2:
$seconds = '02';
break;
case 3:
$seconds = '03';
break;
case 4:
$seconds = '04';
break;
case 5:
$seconds = '05';
break;
case 6:
$seconds = '06';
break;
case 7:
$seconds = '07';
break;
case 8:
$seconds = '08';
break;
case 9:
$seconds = '09';
break;
}

$time = $today[mon] . "-" . $today[mday] . "-" . $today[year] . " " . $hours . ":" . $minutes . ":" . $seconds . " " . $AMPM;

[LordShryku]

varchar's just a bad idea for a date field, but using your format, you could just use this:

$time = date("m-d-Y h:i:s A");

[wanted420]

So if i change it from varchar back to:

QUOTE]signup_date datetime NOT NULL default '0000-00-00 00:00:00',[/QUOTE]

Will it work? or did you just give me a solution for a varchar type and not a datetime type?

EDIT:

Changed it from a varchar to a datetime and it did not submit. All i see is 00-00-0000 00:00:00.

[smoothcriminal]

I believe if you're using the 'datetime' column type you will want to use the code:

$date = date('Y-m-d H:i:s');

With the 'datetime' column type you can easily order your entries by the earliest or oldest date. Also you can choose how your date will be ordered when it is used in your select queries. You can see the MYSQl manual for how to do this.

The value that you are currently seeing is the default value for that column type, which is used if you do not successfully submit an alternate value.

You may find this link useful for date and time questions.

Hope that helps

[wanted420]

Smoothcriminal,

wouldnt that be the same thing as:

LordShryku varchar's just a bad idea for a date field, but using your format, you could just use this:

PHP:

$time = date("m-d-Y h:i:s A");

---

The only diff from yours and his is that you have a diff format to show the date. If i am mistaken please let me know.

if i use timestamp instead of varchar or datetime, how can i edit the format of it. I am using now() to enter in for timestamp. I tried date("m-d-Y h:i:s A"); but it didnt work.

[smoothcriminal]

It's similar, but not the same. The date needs to be formatted the way in which I have in order to be submitted to the 'datetime' column. If the numbers are in the wrong place you will see the '0000-00-00 00:00:00' that you did, as this is the default value for the column type.

Does that explain things a little more clearly?

[wanted420]

Yeah i think. To use the datetime type and date(), it has to be formatted a specific way. Is this the only way to enter into a datetime or is this the way to do it because of how the default is set? Also you have it as 0000-00-00 and i would like it to be 00-00-0000.

Can you tell me how to get it to work with a timestamp too? Same format: 00-00-0000 00:00:00 AM/PM

If you look at the bottom left of each post, where is show what time the post was submitted, that is how i want mine to look. I would like seconds but it does not matter that much.

[LordShryku]

Do yourself a favor right now, and set that field up as a bigint type. Then, just store the unix timestamp(use time()), and format it any way you want when you display it.

[TheDefender]

Amen LordShryku... Always easiest to stor in a UNIX Timestamp format and manipulate it with the date() function later....

Example:

If you store in mm-dd-yyyy hh:mm:ss and want to subtract two dates, then you have to extract them, convert them to a format that can easily be manipulated (UNIX Timestamp) and then do the math, and then convert it back to a readable format.

BUT, if you store in UNIX Timestamp, then you extract it, manipulate it, and then format it to the readable format. AND, you don't have to account for things like the AM or PM, 24hour or 12hour, Beginning or end of year, etc... It's all just seconds since the epoch... MUCH EASIER!