Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases > #512

Re: Storing dates

Cross-posted to 3 groups.

Followups directed to: comp.databases

Show all headers | View raw

[F'up2 comp.databases]

Tim Streater wrote in comp.databases.mysql and comp.lang.php:

> Jerry Stuckle wrote:
>> On 6/21/2014 5:06 PM, Tim Streater wrote:
>> > I have a field which contains values like 3.7, 4.2, etc. But when I
>> > defined the field (in SQLite) I made it a TEXT field, forgetting that I
>> > would need to be able to sort on that field.
>> > 
>> > My workaround is to convert the string to floating at the time of the
>> > sort. In SQLite I do:
>> > 
>> >  ... ORDER BY ROUND(myfield,1);
>> > 
>> > which works just fine. Richard should look for something similar in
>> > mysql.
>> 
>> Why don't you fix your database?
>> 
>> The way you're doing it cannot use an index and will call round() for
>> EVERY row in the sort.
> 
> I'd have to ask a number of users to convert their data when they next
> upgrade the software. Trouble is in SQLite you can't just redefine a
> table column to be a different type; ALTER TABLE is somewhat limited.
> I'd have to copy the whole table to another, DROP the first one, RENAME
> the new one, VACUUM the database to get its size down.

True, but see also “Making Other Kinds Of Table Schema Changes” in 
<http://www.sqlite.org/lang_altertable.html>.

> OK, it's doable,
> but I'm replying on less-than-technically-savvy users to run a Terminal
> script on their own machine to effect this change.

If this is for an Android app (where SQLite is prevalent), you can use 
built-in versioning support to migrate the SQLite database table structure 
automagically with the next upgrade or downgrade.  BTDT.

<http://developer.android.com/guide/topics/data/data-storage.html#db>
<http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html>

Otherwise you can have a table with corresponding version information.  If 
it is missing or the corresponding record is missing, you can assume the old 
format and migrate the data accordingly.  Several SQL-based applications do 
this. 

For example, Magento (which by default is MySQL-InnoDB-based) holds the 
version of an extension in the table `core_resource`, and you can write 
migration scripts (named e.g. upgrade-$old_ver-$new_ver.php) that are 
automatically run if the current version differs but matches $old_ver, and 
the new extension version matches $new_ver, after which the version in 
`core_resource` is updated automatically to $new_ver.


PointedEars 
-- 
Sometimes, what you learn is wrong. If those wrong ideas are close to the 
root of the knowledge tree you build on a particular subject, pruning the 
bad branches can sometimes cause the whole tree to collapse.
  -- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom@94.75.214.39>

Back to comp.databases | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-22 14:38 +0200
  Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-22 16:09 +0100

csiph-web