UNIX_TIMESTAMP ... I get 0 ?

Matth_S

Hi,

I always use the same way to get date from date fields in my database :
I select an UNIX_TIMESTAMP(date) and then I format the date as needed with the php function date().

It has always worked (until now), but in a query, I get 0 with an UNIX_TIMESTAMP though the value of the date isn't NULL in the database. Moreover it works with the function DATE_FORMAT.

Why do I get 0 with UNIX_TIMESTAMP ? I really don't understand.

Thanks

devinemke

i'm assuming you are using mySQL? sounds like your have an invalid date. from the manual:

If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0, but please note that only basic range checking is performed (year from 1970 to 2037, month from 01 to 12, day from 01 from 31).

why are you converting to a unix timestamp anyway? if all of want to do is reformat the date just use mySQL's DATE_FORMAT() function. there is no reason the pull the date out as a unix timestamp and then do the reformatting in PHP (unless you later need to timestamp in PHP).

Matth_S

The date I select isn't out of range (it must be in 2004 I think).

I know that I can use a DATE_FORMAT(), but I think it's more simple for me to get an UNIX_TIMESTAMP and then to convert the date ; cause I want to have the day, month and year of the date in different variables.
If I use an UNIX_TIMESTAMP, I just have to select one field, and then to convert the result of the query using the php function date() :
$day=date('d',$timestamp);
$month=date('m',$timestamp);
etc

If I want to do the same with DATE_FORMAT, I will have to select the date field 3 times as following
DATE_FORMAT('%d',date_field) as day, DATE_FORMAT('%m',date_field) as month
I think the second case is heavier.

But what I really don't understand is that the same kind of query with UNIX_TIMESTAMP upon other tables works perfectly. I know I can get the result with DATE_FORMAT but I don't want to do that way without knowing why it didn't work with the UNIX_TIMESTAMP. There are no reason (I can't see one) the UNIX_TIMESTAMP doesn't work in that case, so until I've found the reason I consider that using the DATE_FORMAT isn't safer ; why should it work in evry case if the UNIX_TIMESTAMP don't ? So I want to prevent any further problem.

laserlight

Well, if I remember correctly, mysql's UNIX_TIMESTAMP gives a fixed length string which can be divided into fixed length substrings that give the year, month etc.

The timestamp that date() uses is the number of seconds from the Unix epoch.

devinemke

Originally posted by laserlight
Well, if I remember correctly, mysql's UNIX_TIMESTAMP gives a fixed length string which can be divided into fixed length substrings that give the year, month etc.

The timestamp that date() uses is the number of seconds from the Unix epoch.

this is not true. although mySQL does have there own TIMESTAMP type which is fixed length (YYYYMMDDHHMMSS) the UNIX_TIMESTAMP() function returns a conventional epoch integer.

as to Matth_S's suggestion that it would be faster to do the formatting in PHP, i disagree. though the difference is probably neglible i think it is always better to do the formatting right from the query (if possible) rather than run the returned data through PHP's date() numerous times.

SELECT YEAR(date), MONTH(date), DAY(date) FROM table

i seem to recall doing some bechmarking on this years ago.