Unix_timestamp

[w00t w00t]

Hey,

Im working on displaying the date on page using a a SQL Query. Im not sure how to get it to print the date in this order month/day/year.

I set it up in phpmyadmin to display the date but it seems like its doing it in reverse. Someone told me to put UNIX_TIMESTAMP but it gives me an error please help.

[lilleman]

Hi,

Which column type are you using?

[w00t w00t]

Im sorry what?

[guelphdad]

is the column you have your date info in actally date type or is it char type?
If it is of date type then you can look up the use of DATE_FORMAT in the manual to give you display options. If your date info is in a char column type you would have to convert the date.

[rogerzebra]

I have a similar problem I hope you dont' mind that I'm using this thread to my problem.
I'm using PHP insert forms which use the US dateformat when they are submitted and I'm trying to convert the dates to european dateformat. (Unix_timestamp) Is there another way to do this than convert the code on each PHP page?.. or is it possible to change the database to using the american dateformat?..anyway I can't get neither to work and showing my PHP code here is probably wrong forum, so but I'm still wonder if it's possible to change MySQL to use US dateformat.
I'm greatful for any comments.
cheers
/rz

[Crowe]

I never store date formats in any country format. I store them in unix timestamp (epoch time) and then convert them on the fly to any date format I need. unix timestamp is the number of seconds since 00:00:00 GMT, January 1, 1970.

MySQL has a date colum that is in its own format. It sounds like your storing a string formatted for us time format. You can use strtotime() in php to convert the string to unix timestamp and then php can format it anyway you want, like so:

$formatted_date = date("m/d/Y", $unformatted_date); 

Your date would then look like 2/10/2005 if the $unformatted_date was for today. You can look ate the date() function for more formatting options.

Hope that helps.

[r937]

I never store date formats in any country format.

that's because you can't anyway

mysql stores dates in its own internal format, which happens to be 3 bytes -- yes, 3 bytes, which is why it's wrong to suggest that it's stored in this format or that format or any particular format

microsoft access and sql server, by the way, use whole integer numbers to store dates and times, one number for the date and one for the time -- they are similar to unix time but based on a different zero date

one thing to take into consideration is whether you would need to do a conversion on a stored database date or datetime value in order to do a search

if you wanted to find all rows which had an effective date of February, and the dates were all stored in unix times, you have to be very careful never to apply a conversion function to the database table column, or else guess what -- inefficient table scan!!

you need to make sure that your WHERE clauses have just the "naked" column on one side of the condition

bad --> where month(from_unixtime(mycolumn)) = 02

good --> where mycolumn between 1107241200 and 1109660399

otherwise, the database engine can't use the index on the column

[rogerzebra]

Crowe,
Believe me I've tried all different solutions the last two days whitout any luck including strtotime function. I tried your code now it didn't work..am I missing something??

[rogerzebra]

Wow r937,
thanks for your lesson. so what's the best datatype to store date values in, when I'm using select and insert pages driven by PHP in US dateformat?..wow.. did you got that?

[r937]

by far the best format to store dates in is DATE

to insert date values, you have a choice of format --

- a string 'YYYY-MM-DD '
- a string 'YY-MM-DD'
- a string 'YYYYMMDD'
- a string 'YYMMDD'
- a number YYYYMMDD
- a number YYMMDD

to display a date, you can either do the formatting in your scripting language, or else you can let mysql format it for you into a string using the DATE_FORMAT function

i recommend reading up on all of the mysql date functions -- some of them are fantastic

[rogerzebra]

Thanks for your lessons on this r937,
That really helped and maked that part more clear to me. It still leave me with my original problem though, to convert the submission form from US dateformat to european format. I'll continue my struggle.
/rz

[guelphdad]

input the date in yyyy-mm-dd format as told by Rudy, then check the fourth item in this thread where I suggested you look up DATE_FORMAT in the manual.

[rogerzebra]

okay,
I think this needs an explanation. You see I have no problem to save the data in european format. and believe me I have look up and tried the date_format function.
when I submit my update form, it register todays date even if I put a date from last month..regardless what I type in it only register todays date. Is that what the date format function supposed to do?

[r937]

no, the DATE_FORMAT function is to choose the way todisplay (output) your dates

to insert (input) date values, you have only these choices of format --

- a string 'YYYY-MM-DD '
- a string 'YY-MM-DD'
- a string 'YYYYMMDD'
- a string 'YYMMDD'
- a number YYYYMMDD
- a number YYMMDD