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

Groups > comp.databases > #512 > unrolled thread

Re: Storing dates

Back to article view | Back to comp.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  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

#512 — Re: Storing dates

[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>

[toc] | [next] | [standalone]

#515

In article <2353292.cnrgqz3L4O@PointedEars.de>, Thomas 'PointedEars'
Lahn <PointedEars@web.de> wrote:

> [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>.

Yes, the steps given there are essentially those I briefly outlined
above.

> Otherwise you can have a table with corresponding version information.

I came to that when considering how to handle table updates that a new
version of the app might require. It was easy enough to ensure that
such an update only gets applied once.

(nothing to do with Android, BTW).

-- 
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases

csiph-web