Page 1 of 1
Quickie: NOW()
Posted: Wed Aug 13, 2003 1:31 pm
by mikusan
That's right, don't waste time, do it do it NOW()
Okay, my problem is quite straightforward... it's about inputting a datetime compatible with MySQL into the database
this is the way i did it:
Code: Select all
$datetime = date("Y-m-d H:i:s");
//Then just mysql_query("INSERT ....
Though i would like to do it in one shot, just using MySQL query to add the datetime stamp, i think it's more efficient. I guess that what i have to use is NOW() function, but saying:
Code: Select all
mysql_query("SELECT NOW() AS date_time FROM ct_news");
Seems VERY VERY strange to me, at least i would immgine to use some sort of INSERT query... nevertheles please help, date_time is my database field of type "datetime" everything except the query is ready.
Thanks in advance
Posted: Wed Aug 13, 2003 3:17 pm
by m3rajk
you CAN use now in statements.. the issue is that now has a specific format, and is relative to the sever it executes on. how is the time you're looking at? relative to gmt?
Posted: Wed Aug 13, 2003 3:28 pm
by xisle
inserting the NOW() value would look something like this...
Code: Select all
mysql_query("INSERT into ct_news(fieldname1,fieldname2)VALUES(now(),'$somethingelse')");
Posted: Wed Aug 13, 2003 3:40 pm
by mikusan
Thanks xisle... i will try that out... I wonder why they didn;t include that in the MySQL manual, or im just dumb...
As to
m3rajk what were you thinking, i mean what if i wanted the time in gmt, what if not? I think you have already dealed with this issue, could you shed some light on me too

Posted: Wed Aug 13, 2003 3:53 pm
by m3rajk
i actually have. i am trying ot make the db now, but after finishing the php and getting the db statments in to prepp for the db i looked at what sql could do with time. while i had to fine out use of now() in the statement from others because it's not in any books i've seen (their failure, there's already two that wanted to know that, means there must be more, we're not dumb) i found out that NOW() is current to your server. that there is no gmt time. that since i want to have certain things get put in in a way i can manipulate to whoever calls it regaurdless of timezone, so that it looks as if it's THEIR timezone (ie: friends in australia wont be posting with timestamp 12+ hours back while i'm doing the development tweaking it to go find a actual host), i need to use gmt.. ok, so that meanss a gmt offset feild sshould be included. default it to yourself or to 0.
then the user customizes the site timestamps to themselves. but the only way to do that is php.
to save as a gmt unix epoch you have to use either a string or... i think a int, and use the call to time()
otherwise, use time() in a call to gmdate()
so you use gmdate("format to save", time())
that gets you what you want.
let me modify something and then you can go to a url i'll send ya in a sec in a pm.
Posted: Wed Aug 13, 2003 4:12 pm
by mikusan
Now out of curiosity, i am also trying to figure out how to use indexes, and ATM i am really confused, yes i have used indexes to index tables that have some sort of userID or something ID or basically a field with an autoincrement. Just a trick i learned on the way, though i am not sure why i do it

i feel foolish to say, nevertheless if someone could give me an insight on how they work, i have read MySQL manual, but i am not quite sure that i understood it well.
Say that i will have a HUGE links section, where fields are stored by name type, datetime, user, link, and maybe i should use an autoincerement type field, but i figure since I will display data in chronological order, i don't need that field (?)
How do indexes work, and in my case which key should be my primary, and which is the index?
Thanks!!
Posted: Wed Aug 13, 2003 4:13 pm
by JAM
Just a sidenote... Also have in mind that you can take the advantage of unix_timestamp ;
Code: Select all
// mysql query "select now()"
// Result: 2003-08-13 23:06:12
// mysql query "select unix_timestamp(now())"
// Result: 1060808718
echo time();
// Result: 1060808718
...that will return a result matching time().
note: Values in example are not the same as I just typed something, so dont compare the first example with the other two in any way...
Posted: Wed Aug 13, 2003 4:20 pm
by m3rajk
actually trying that in the system doesn't work as nicely as you'd think. it give s a timestamp, but not the type you cna use to modify to a user's location, ehich is the point of my post, that if he's modifying ot locations that he CANNOT use now() because now() is relative to the server it runs on
Posted: Wed Aug 13, 2003 4:24 pm
by m3rajk
mikusan:
simply put you can search on anything in the table UNLESS you have fulltext, inwhich case youcan only do what the FULLTEXT statements are.
keys (indexes) are optimiszed for searching, so it's better to use them. unique means that no two items in a feild can have the same value. a key can combine feilds. a unique key cannot have two things that are identical, a primary key is a unique key that cannot be null.
if you look at my posts in this forum it might help
Posted: Wed Aug 13, 2003 4:37 pm
by JAM
m3rajk wrote:actually trying that in the system doesn't work as nicely as you'd think. it give s a timestamp, but not the type you cna use to modify to a user's location, ehich is the point of my post, that if he's modifying ot locations that he CANNOT use now() because now() is relative to the server it runs on
Hence the "sidenote" note...
Posted: Wed Aug 13, 2003 4:44 pm
by mikusan
Thanks
m3rajk
I have throughly read your link, I think i am beginning to get it...
I am just curious where exactly did you use the code, When i thought of it, i don't think it's a bad idea to have the time and date of the server (wherever it is) because in some sense it is consistent, every date and time will be stamped equally, so...
I thought it would be VERY nice to use it the way you did in the example you gave me, that is to use them in conjunction with each other, to show for example that a user posted a story at what time, and the difference with the server time i mean it would look quite pro to see August 13, 2003 - 11:35
+4 hehe if you know where i am getting at
how do you plan to use it?
A quick look at the bible showev the following results:
checkdate -- Validate a gregorian date
date -- Format a local time/date
getdate -- Get date/time information
gettimeofday -- Get current time
gmdate -- Format a GMT/UTC date/time
gmmktime -- Get UNIX timestamp for a GMT date
gmstrftime -- Format a GMT/UTC time/date according to locale settings
localtime -- Get the local time
microtime -- Return current UNIX timestamp with microseconds
mktime -- Get UNIX timestamp for a date
strftime -- Format a local time/date according to locale settings
strtotime -- Parse about any English textual datetime description into a UNIX timestamp
time -- Return current UNIX timestamp
I found that there are many ways to find out the gmt/utc datetimes, and perhaps they can be alittle simpler than your string replacements but i have not yet read into them so i am just giving a preview, in search of opinion...
Posted: Thu Aug 14, 2003 8:16 am
by mikusan
Last question on this topic:
Is it a bad idea to use the datetime stamp as the primary index??
Posted: Thu Aug 14, 2003 9:37 am
by nielsene
Please, please, please DON"T equate KEY with INDEX
I know MySQL does a dis-service here by misusing the terms, but it will hurt you in the long run.
A (Candidate) Key is a column(s) whose value uniquely identifies a row of a table. A Primary Key is a candidate key that has been labeled as "more importanrt" than the other keys. However all keys must be unique. A key is an abstraction at the logical level of the database.
An index is a mechanism at the physical level of the database that can enhance preformance. In a general sense an Index is a paired listing of
index value:primary key typically arranged in a b-tree (or hash) representation which allows for extremely fast lookups and comparisons on the index. For the index of a primary key, there is simply a b-tree of key values (index value=primary key in this case).
[end rant]
Answering the question about using a datetime stamp as the primary index.
Well its probably a great idea to INDEX on the datetime stamp. Its probably a hideous idea to to make a datetime stamp a primary key.
Reasoning: you will commonly be searching/sorting on the datetime stamp therefore it should be indexed. You can not ensure that two items will not share the same timestamp therefore it should not be a key.