Saving data to a timestamp field ignores microseconds. #776

Closed
Speedrockracer opened this Issue Jan 7, 2013 · 10 comments

Comments

Projects
None yet
5 participants
@Speedrockracer

Heya,

Data given to the model:
array (size=4)
'content' => string 'test' (length=4)
'user_id' => string 'UCR76EQO' (length=8)
'timestamp' => string '2013-01-07 13:57:03.621684' (length=26)

Resulting query of $model->save():
INSERT INTO "sq_message" ("timestamp", "content", "user_id") VALUES ('2013-01-07 13:54:48', 'test', 'UCR76EQO');

As you can see the micro seconds are not in the query and thus not saved! Im using postgres as database.
The field in the database is defined as:
"timestamp" timestamp without time zone NOT NULL DEFAULT now()

Greetz,
Speedrock

@mackstar

This comment has been minimized.

Show comment
Hide comment
@mackstar

mackstar Jan 7, 2013

Member

A unix timestamp only goes as far as seconds, you should use a different field if you need milliseconds.

Member

mackstar commented Jan 7, 2013

A unix timestamp only goes as far as seconds, you should use a different field if you need milliseconds.

@Speedrockracer

This comment has been minimized.

Show comment
Hide comment
@Speedrockracer

Speedrockracer Jan 7, 2013

It is a postgres timestamp and I am using the correct format for that.
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html
I dont use unix timestamps anywhere...

It is a postgres timestamp and I am using the correct format for that.
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html
I dont use unix timestamps anywhere...

@mackstar

This comment has been minimized.

Show comment
Hide comment
@mackstar

mackstar Jan 7, 2013

Member

OK thanks for clarifying that!

Member

mackstar commented Jan 7, 2013

OK thanks for clarifying that!

@Speedrockracer

This comment has been minimized.

Show comment
Hide comment
@Speedrockracer

Speedrockracer Jan 7, 2013

Np ^^
I do think that this had something to do with the unix timestamp. Maybe lithium converts it somwhere. Or maybe it uses the php DateTime class (that doesn't work properly with microseconds!)

But I didn't go into it deep enough the verifiy that.

Greetz,
Speedrock

Np ^^
I do think that this had something to do with the unix timestamp. Maybe lithium converts it somwhere. Or maybe it uses the php DateTime class (that doesn't work properly with microseconds!)

But I didn't go into it deep enough the verifiy that.

Greetz,
Speedrock

@jails

This comment has been minimized.

Show comment
Hide comment
@jails

jails Jan 7, 2013

Contributor

Because of the wide variety of date format over databases, I'm fine with the fact that the ORM use the lowest common denominator for managing the 'datetime' type (I mean the framework 'datetime' type here). Moreover this allow to switch over RDBMS with relative serenity.

The crappy workaround would be to: set your own $_schema in the model and using 'string' instead of 'datetime' for the concerned field to not be "annoyed" by the default behavior.

Or you can also extends/PR the core PostgreSql database adatper and add a new entry in PostgreSq::_columns, with an arbitrary name, not depending on the 'formatter' => 'date' since it currenlty based on strtotime (or change this behavior). Don't forget here to also change the PostgreSq::_column() to make it detect timestamp(p) as your new created entry.

Contributor

jails commented Jan 7, 2013

Because of the wide variety of date format over databases, I'm fine with the fact that the ORM use the lowest common denominator for managing the 'datetime' type (I mean the framework 'datetime' type here). Moreover this allow to switch over RDBMS with relative serenity.

The crappy workaround would be to: set your own $_schema in the model and using 'string' instead of 'datetime' for the concerned field to not be "annoyed" by the default behavior.

Or you can also extends/PR the core PostgreSql database adatper and add a new entry in PostgreSq::_columns, with an arbitrary name, not depending on the 'formatter' => 'date' since it currenlty based on strtotime (or change this behavior). Don't forget here to also change the PostgreSq::_column() to make it detect timestamp(p) as your new created entry.

@Speedrockracer

This comment has been minimized.

Show comment
Hide comment
@Speedrockracer

Speedrockracer Jan 8, 2013

Heya,

Unfortunately this is not a small annoyance to me since I have to log certain events and the order of those events is very important. While testing there where a lot of cases where the events occurred in the same second thus ruining the order!

I implemented your "crappy" workaround with success! Thanks.

However the microseconds are in the sql standard for datetime and there is currently no RDBMS supported in lithium that would break, Sqllite does not have a timestamp field (their datetime functions do support it) and MySql simply ignores the microseconds.
But there are probably more important things to take care of right now so consider it a feature request then.

Heya,

Unfortunately this is not a small annoyance to me since I have to log certain events and the order of those events is very important. While testing there where a lot of cases where the events occurred in the same second thus ruining the order!

I implemented your "crappy" workaround with success! Thanks.

However the microseconds are in the sql standard for datetime and there is currently no RDBMS supported in lithium that would break, Sqllite does not have a timestamp field (their datetime functions do support it) and MySql simply ignores the microseconds.
But there are probably more important things to take care of right now so consider it a feature request then.

@jails

This comment has been minimized.

Show comment
Hide comment
@jails

jails Jan 8, 2013

Contributor

Thanks for the feedback on this !

Contributor

jails commented Jan 8, 2013

Thanks for the feedback on this !

@nateabele

This comment has been minimized.

Show comment
Hide comment
@nateabele

nateabele Jan 16, 2013

Member

Easier custom data types are coming in the future. Hang tight. :-)

Member

nateabele commented Jan 16, 2013

Easier custom data types are coming in the future. Hang tight. :-)

@al-the-x

This comment has been minimized.

Show comment
Hide comment
@al-the-x

al-the-x Mar 8, 2013

Contributor

I think that #855 is related and could solve this issue for the OP. Please review.

Contributor

al-the-x commented Mar 8, 2013

I think that #855 is related and could solve this issue for the OP. Please review.

@jails

This comment has been minimized.

Show comment
Hide comment
@jails

jails Apr 13, 2013

Contributor

Closed in flavor of #858.

Contributor

jails commented Apr 13, 2013

Closed in flavor of #858.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment