Hi,
I was wondering if there was a reason why the date fields are stored as char(14), doesn't that make them a string? I want to analyse the dates things happened - between ranges etc, and could do with changing them to a double or big int or even a mysql date! Storing them in a numeric format would also increase performance marginally, as the field size would become much smaller. Am I missing something important about MySQL/mediawiki date handling? Will MW break if I change the underlying format? I have never seen a non numeric string in a timestamp...
Kind regards,
Alex
On 21/08/06, Alex Powell alexp700@gmail.com wrote:
I was wondering if there was a reason why the date fields are stored as char(14), doesn't that make them a string? I want to analyse the dates things happened - between ranges etc, and could do with changing them to a double or big int or even a mysql date! Storing them in a numeric format would also increase performance marginally, as the field size would become much smaller. Am I missing something important about MySQL/mediawiki date handling? Will MW break if I change the underlying format? I have never seen a non numeric string in a timestamp...
This is documented in maintenance/tables.sql - check the comments at the top, it has something about timestamp fields.
Rob Church
Hi,
This is documented in maintenance/tables.sql - check the comments at the top, it has something about timestamp fields.
Generally someone ages ago didn't like that TIMESTAMPs are auto- updated in MySQL and chose CHAR(14) instead of DATETIME. It would be pretty good idea to switch some day to datetime, and we have nearly all code in there. The sole problem is tables with tens of millions of rows to change. But that is doable, I suppose.
And in case anyone needs a cast, one can do that with say... ourfield +interval 0 second ;-)
Best regards,
On 21/08/06, Domas Mituzas midom.lists@gmail.com wrote:
Hi,
This is documented in maintenance/tables.sql - check the comments at the top, it has something about timestamp fields.
Generally someone ages ago didn't like that TIMESTAMPs are auto- updated in MySQL and chose CHAR(14) instead of DATETIME. It would be pretty good idea to switch some day to datetime, and we have nearly all code in there. The sole problem is tables with tens of millions of rows to change. But that is doable, I suppose.
With Domas to read for them, I think the future of literature and the English language is sound.
Rob Church
Hi!!!!
With Domas to read for them, I think the future of literature and the English language is sound.
?
On 21/08/06, Domas Mituzas midom.lists@gmail.com wrote:
Hi!!!!
With Domas to read for them, I think the future of literature and the English language is sound.
?
I didn't realise it was international punctuation mark week. I think I'll opt for the apostrophe.
Domas, it was light sarcasm.
Rob Church
Domas Mituzas wrote:
Hi!!!!
With Domas to read for them, I think the future of literature and the English language is sound.
?
I think his attempted joke might have meant smoething like this: * Fewer and fewer people _read_ (especially instructions & manuals) * Thus, more and more people think it's pointless to write anything * But fortunately, there's Domas who still reads things! * Therefore, people will continue to write.
Or something. :) Timwi
"Timwi" timwi@gmx.net wrote in message news:ecfgto$a0c$3@sea.gmane.org...
Domas Mituzas wrote:
Hi!!!!
With Domas to read for them, I think the future of literature and the English language is sound.
?
I think his attempted joke might have meant smoething like this:
- Fewer and fewer people _read_ (especially instructions & manuals)
- Thus, more and more people think it's pointless to write anything
- But fortunately, there's Domas who still reads things!
- Therefore, people will continue to write.
Or something. :) Timwi
Nope - I didn't get it either. :-)
- Mark Clements (HappyDog)
Domas Mituzas wrote:
Hi,
This is documented in maintenance/tables.sql - check the comments at the top, it has something about timestamp fields.
Generally someone ages ago didn't like that TIMESTAMPs are auto- updated in MySQL and chose CHAR(14) instead of DATETIME.
I have heard that the culprit was Lee and that the problem was some kind of unspecified data import problem.
It would be pretty good idea to switch some day to datetime, and we have nearly all code in there. The sole problem is tables with tens of millions of rows to change. But that is doable, I suppose.
And in case anyone needs a cast, one can do that with say... ourfield +interval 0 second ;-)
I've never really felt the need to change it, personally. It's not like changing it would enable any extra features or anything. You can convert between the representations easily enough, in either PHP or SQL.
-- Tim Starling
Hi!
I have heard that the culprit was Lee and that the problem was some kind of unspecified data import problem.
Well, TIMESTAMP is magic field which has too much magic in it, and the magic was less controllable before 4.1. The first timestamp field would auto-update itself on both inserts or updates (if not referenced directly). Second timestamp field would be set to current timestamp only on inserts. So if you, say, reset page_random, and have TIMESTAMP field, you'd be ending up with all fields loosing time information.
I've never really felt the need to change it, personally. It's not like changing it would enable any extra features or anything. You can convert between the representations easily enough, in either PHP or SQL.
Um, yeah, there would not be much difference of functionality (you can cast, anyway ;-). The only question is efficiency - 8 (or 4 in case of usable 4.1 TIMESTAMP) bytes instead of 14, and somewhat easier range scans.
It is not that it is the most significant issue at the moment, hence it stays there for a while.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
I was wondering if there was a reason why the date fields are stored as char(14), doesn't that make them a string? I want to analyse the dates things happened - between ranges etc, and could do with changing them to a double or big int or even a mysql date!
For what it's worth, if you use MediaWiki with a Postgres database, dates *are* stored as true timestamp fields. The only big problem I found when converting them over was with the archive table, which still needs to at least emulate a char(14) due to the URLs generated within the code. Just a heads up for when/if the timestamp changes for the MySQL version.
- -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200608210919 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Greg Sabino Mullane wrote:
For what it's worth, if you use MediaWiki with a Postgres database, dates *are* stored as true timestamp fields. The only big problem I found when converting them over was with the archive table, which still needs to at least emulate a char(14) due to the URLs generated within the code.
Er... no?
If it looks like it does, that's a bug in Special:Undelete and should be fixed.
-- brion vibber (brion @ pobox.com)
Brion Vibber wrote:
Greg Sabino Mullane wrote:
For what it's worth, if you use MediaWiki with a Postgres database, dates *are* stored as true timestamp fields. The only big problem I found when converting them over was with the archive table, which still needs to at least emulate a char(14) due to the URLs generated within the code.
Er... no?
If it looks like it does, that's a bug in Special:Undelete and should be fixed.
Specifically, it should be using wfTimestamp( TS_MW, $blah ) rather than just spitting out timestamp field values into URLs directly.
-- brion vibber (brion @ pobox.com)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
For what it's worth, if you use MediaWiki with a Postgres database, dates *are* stored as true timestamp fields. The only big problem I found when converting them over was with the archive table, which still needs to at least emulate a char(14) due to the URLs generated within the code.
Er... no?
If it looks like it does, that's a bug in Special:Undelete and should be fixed.
That's good to hear. From what I recall, there was some code inside of SpecialUndelete.php that I did not want to mess with, so I made the workaround. I'm swamped this week, but if someone wants to take a look, I know it's related to the "timestamp" URL inside the list of deleted pages, e.g.:
http://en.wikipedia.org/w/index.php?title=Special:Undelete&target=Evil_f...
- -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200608231709 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
wikitech-l@lists.wikimedia.org