Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I found a workaround! It is very clean and doesn't require any application code changes. This works for Doctrine, and can be applied to other ORM's as well. </p> <p>Basically, store the timestamp as a string. </p> <p>Comparisons and sorting works if the date string is formatted correctly. MySQL time functions will truncate the microsecond portion when passed a date string. This is okay if microsecond precision isn't needed for <code>date_diff</code> etc.</p> <pre><code>SELECT DATEDIFF('2010-04-04 17:24:42.000000','2010-04-04 17:24:42.999999'); &gt; 0 SELECT microsecond('2010-04-04 17:24:42.021343'); &gt; 21343 </code></pre> <p>I ended up writing a <code>MicroTimestampable</code> class that will implement this. I just annotate my fields as <code>actAs:MicroTimestampable</code> and voila, microtime precision with MySQL and Doctrine.</p> <p><b>Doctrine_Template_MicroTimestampable</b></p> <pre><code>class Doctrine_Template_MicroTimestampable extends Doctrine_Template_Timestampable { /** * Array of Timestampable options * * @var string */ protected $_options = array('created' =&gt; array('name' =&gt; 'created_at', 'alias' =&gt; null, 'type' =&gt; 'string(30)', 'format' =&gt; 'Y-m-d H:i:s', 'disabled' =&gt; false, 'expression' =&gt; false, 'options' =&gt; array('notnull' =&gt; true)), 'updated' =&gt; array('name' =&gt; 'updated_at', 'alias' =&gt; null, 'type' =&gt; 'string(30)', 'format' =&gt; 'Y-m-d H:i:s', 'disabled' =&gt; false, 'expression' =&gt; false, 'onInsert' =&gt; true, 'options' =&gt; array('notnull' =&gt; true))); /** * Set table definition for Timestampable behavior * * @return void */ public function setTableDefinition() { if ( ! $this-&gt;_options['created']['disabled']) { $name = $this-&gt;_options['created']['name']; if ($this-&gt;_options['created']['alias']) { $name .= ' as ' . $this-&gt;_options['created']['alias']; } $this-&gt;hasColumn($name, $this-&gt;_options['created']['type'], null, $this-&gt;_options['created']['options']); } if ( ! $this-&gt;_options['updated']['disabled']) { $name = $this-&gt;_options['updated']['name']; if ($this-&gt;_options['updated']['alias']) { $name .= ' as ' . $this-&gt;_options['updated']['alias']; } $this-&gt;hasColumn($name, $this-&gt;_options['updated']['type'], null, $this-&gt;_options['updated']['options']); } $this-&gt;addListener(new Doctrine_Template_Listener_MicroTimestampable($this-&gt;_options)); } } </code></pre> <p><b>Doctrine_Template_Listener_MicroTimestampable</b></p> <pre><code>class Doctrine_Template_Listener_MicroTimestampable extends Doctrine_Template_Listener_Timestampable { protected $_options = array(); /** * __construct * * @param string $options * @return void */ public function __construct(array $options) { $this-&gt;_options = $options; } /** * Gets the timestamp in the correct format based on the way the behavior is configured * * @param string $type * @return void */ public function getTimestamp($type, $conn = null) { $options = $this-&gt;_options[$type]; if ($options['expression'] !== false &amp;&amp; is_string($options['expression'])) { return new Doctrine_Expression($options['expression'], $conn); } else { if ($options['type'] == 'date') { return date($options['format'], time().".".microtime()); } else if ($options['type'] == 'timestamp') { return date($options['format'], time().".".microtime()); } else { return time().".".microtime(); } } } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhy doesn't MySQL support millisecond / microsecond precision?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USByron Whitlock
    1. USByron Whitlock
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy doesn't MySQL support millisecond / microsecond precision?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou are right about this solution to be working. However, the lookup and comparison times (for strings) will be much longer than comparing integers.
      singulars
      1. PO
      1. USEtamar Laron
    2. COHow so if the field is indexed?
      singulars
      1. PO
      1. USByron Whitlock
    3. COAn integer is simple, much less data to deal with in every case, "Hi There" is much more expensive to process than 1871239471294871290843712974129043192741890274311234 is. Integers are much more scalable, especially when being used in comparisons, more CPU cycles per comparison for anything but the shortest string. Indexing helps, but it doesn't change that you're dealing with more data on the comparisons, at least at some point.
      singulars
      1. PO
      1. USNick Craver
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload