SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
Thread: Unix_timestamp
-
Feb 7, 2005, 18:16 #1
- Join Date
- Jan 2005
- Location
- Hawai'i
- Posts
- 15
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Unix_timestamp
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.
-
Feb 7, 2005, 18:32 #2
- Join Date
- Feb 2004
- Location
- Örebro, Sweden
- Posts
- 2,716
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi,
Which column type are you using?ERIK RIKLUND :: Yes, I've been gone quite a while.
-
Feb 7, 2005, 20:26 #3
- Join Date
- Jan 2005
- Location
- Hawai'i
- Posts
- 15
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Im sorry what?
-
Feb 7, 2005, 23:21 #4
-
Feb 10, 2005, 16:11 #5
- Join Date
- Aug 2004
- Location
- san diego
- Posts
- 26
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 10, 2005, 16:18 #6
- Join Date
- Nov 2001
- Location
- Huntsville
- Posts
- 1,117
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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:
PHP Code:$formatted_date = date("m/d/Y", $unformatted_date);
Hope that helps.Chrispian H. Burks
Nothing To Say
-
Feb 10, 2005, 16:31 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Crowe
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
-
Feb 10, 2005, 16:33 #8
- Join Date
- Aug 2004
- Location
- san diego
- Posts
- 26
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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??
-
Feb 10, 2005, 16:41 #9
- Join Date
- Aug 2004
- Location
- san diego
- Posts
- 26
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Feb 10, 2005, 16:47 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Feb 10, 2005, 17:23 #11
- Join Date
- Aug 2004
- Location
- san diego
- Posts
- 26
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 10, 2005, 20:00 #12
-
Feb 11, 2005, 17:10 #13
- Join Date
- Aug 2004
- Location
- san diego
- Posts
- 26
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Feb 11, 2005, 21:15 #14
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
Bookmarks