Issue with DATETIME

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Issue with DATETIME

Post by Slyvampy »

Im sorry to be a pain, but once again this haunts me.

Im trying to insert a DATETIME into a mySQL database, however everytime I put one it in comes up either a stupid date, or 0000-00-00 :00:00:00

Tried this...

Code: Select all

list($month, $day, $year) = split('ї/.-]', $member_date_of_birth);
Tried this...

Code: Select all

$member_date_of_birth = ("12/Jun/2002:01:02:26");
And this...

Code: Select all

mktime
and this...

Code: Select all

strtotime
I can get dates out and sort them, but i need to get them into the database as a DATETIME.

I've tried converting the string to time, converting it into a UNIX timestamp, and even a string "1980-10-23 00:00:00" and nothing works.

I can put a date in using myPhpAdmin but the code it uses looks like a string.

Can anyone help?

Cheers,

SteJ.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The DATETIME field takes the date as 'YYYY-MM-DD HH:MM:SS' so you need to format your date this way. You can do this using the date() function:

Code: Select all

// Create the MySQL formatted date
$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
$mysql_datetime = date('Y-m-d H:i:s', $timestamp);

// Put this into the database
$sql = "INSERT INTO table (birthdaytime) VALUES('$mysql_datetime')";
mysql_query($sql) or die(mysql_error());
Note though that timestamps can only go back as far as 1970 - if you are trying to format dates from before then then you need to use a different approach.

Mac
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Post by Slyvampy »

Cheers, that works brillantly,

However like you said it dont work for dates previous from 1970.

So whats the best way to store a date so that it can be orderd via date.

I was told this was one of the best ways to get a date from a database, when ORDER BY.

is UNIX TIMESTAMP better? if so how could i implement that backwards

if i use the UNIX TIMESTAMP, ie. second from that date right?

TIMESTAMP = 00000000000000 seconds

I want to get the info out of the database how do i correct do that, this is my statement at the moment.

Code: Select all

$returned = mysql_query("SELECT DATE_FORMAT(date_of_news, '%d-%m-%Y') AS date_of_birth, full_name, member_type, details FROM members WHERE member_type = 'admin' ORDER BY `date_of_birth` DESC LIMIT 0 , 5", $connection) or die("Search Failed, please try again later");
now that works with DATETIME, but how would i get the same effect with UNIX TIMESTAMP ?

I need to be flexible with the date also
i.e.

need

$day
$month
$year

so i can format it sometimes like

23rd October 1980
or
23-10-1980

and so i can perform action upon the date like
how old some-one is, from the $year to this year.

SteJ.
Last edited by Slyvampy on Fri Mar 07, 2003 4:27 am, edited 1 time in total.
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Post by Slyvampy »

I need to be flexible with the date also
i.e.

need

$day
$month
$year

so i can format it sometimes like

23rd October 1980
or
23-10-1980

and so i can perform action upon the date like
how old some-one is, from the $year to this year.
User avatar
Kriek
Forum Contributor
Posts: 238
Joined: Wed May 29, 2002 3:46 am
Location: Florida
Contact:

negative timestamps

Post by Kriek »

PHP Extension and Application Repository aka PEAR offers a Date() and a Date_Calc() class which is capable of handling dates pre 1970 and post 2038. PEAR::Date is part of the standard PHP distribution. The problem is that all of the PHP date functions are based on that miserable timestamp.
User avatar
Slyvampy
Forum Newbie
Posts: 23
Joined: Thu Nov 28, 2002 2:03 am
Location: Yorkshire, England
Contact:

Please HELP, TIMESTAMP vs mySQL & PHP = PROBLEM

Post by Slyvampy »

I agree.

However I have been doing nothing but reading for the last few days about timestamp.

I read this:-
The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
which would be perfect, but i cant set the database field to date. Im using phpMyAdmin, and there is no date function.

so i tried
TIMESTAMP( 8 ) YYYYMMDD
which I dont mind using just a bit of parsing to get the right date, however when INSERTING into my database it wont let me.

so i thought and read some more, that my string

Code: Select all

$date_of_birth
has to be a vaild date.

so i set off with both maketime and strtotime
(i was told strtotime was faster, but im not intrested in speed really)

Code: Select all

list($day, $month, $year) = explode("-", $date_of_birth);

$updated_date_of_birth = ("$year-$month-$day");

$new_date_of_birth = mktime (0,0,0,$month,$day,$year);
nope that didnt work, just outputted a series of numbers..

then

Code: Select all

list($day, $month, $year) = explode("-", $date_of_birth);

$updated_date_of_birth = ("$year-$month-$day");

$new_date_of_birth = strtotime("$updated_date_of_birth");
nope that didnt work.

Then i thought going back to the linux timestamp 'was this series of numbers the amount of seconds past'?

nope, however doing the same back work, that is "strftime"

Im completely lost. and confused. mySQL says timestamp is YYYYMMDD yet php is outputing a series of numbers 360545600.

This date thingy is done everywhere so why am i having such a problem with it?
toms100
Forum Contributor
Posts: 119
Joined: Wed Feb 26, 2003 10:29 am
Location: Bristol,UK

Post by toms100 »

it might be a bit better to store the date as a number as generated by
time();
then you can sort it ascending descending etc in the database (just in a normal text mysql colomn), and when you want it back to a date you can do this:

Code: Select all

<?php
function ConvertTime($t) {
	$today = getdate($t);
	$dateString = $today['mday']."/".$today['mon']."/".$today['year'];// Text string containing the date
	return $dateString;
}

$t = time(); // could be the number stored in a database
$thedate = ConvertTime($t);
echo $thedate;
?>
Post Reply