On Nov 30, 6:23 pm, Alex <[email protected]> wrote:
> Hi everybody,
>
> I noticed recently that insert and create literalize same time stamp
> differently. Here is my example.
>
> DB.create_table? :tests do
> primary_key :id
> DateTime :posted_at
> end
>
> class Test < Sequel::Model
> end
>
> date = '2011-11-10T00:00:00-00:00'
>
> Test.create({:posted_at=>date})
> produces => INSERT INTO [TESTS] ([POSTED_AT]) OUTPUT [INSERTED].*
> VALUES ('2011-11-09 18:00:00.000')
> which is correct sequel converts input time stamp to local CST from
> UTC
>
> Test.insert({:posted_at=>date})
> produces => INSERT INTO [TESTS] ([POSTED_AT]) VALUES
> (N'2011-11-10T00:00:00-00:00')
> there is no timezone conversion and SQL generates exception when
> executed
> NativeException: com.microsoft.sqlserver.jdbc.SQLServerException:
> Conversion failed when converting date and/or time from character
> string.
As the exception message indicates, Microsoft SQL Server doesn't like
that timestamp format (apparently due to the timezone offset). The
reason it works for create is that with models, data is typecasting
before being used. So your date string is parsed using ruby's date/
time parsing routines to create a ruby Time object before being
inserted. It's not that they literalize differently (they don't),
it's that model code does typecasting before literalizing.
You should get roughly the same results with insert if you provide a
Time value instead of a string:
DB[:tests].insert({:posted_at=>Time.parse(date).getlocal})
> Same result observed when DB insert is used
> DB[:tests].insert({:posted_at=>date})
Test.insert is the same as Test.dataset.insert, which is why you get
the same results.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
