# Saving XMLSchema datetime into sqlite/mysql

**URL:** <https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931>\
**Category:** Rails\
**Created:** [July 20, 2010, 10:32pm UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931 "2010-07-20T22:32:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 20, 2010, 10:32pm UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/1 "2010-07-20T22:32:34Z")

</div>

Hello everyone,

I’ve been struggling with something that is probably very simple.

I need to save an entry from an atom feed that has a datetime string  
like this:  
2010-07-20T11:49:19.000Z  
which is an XMLSchema date time.

I’ve tried many variations around:

glog.elements.each(“published”) do |element|  
@snapshot.entrycreated =  
Time.xmlschema(element.text).to\_s(:db)  
end

If I save that to a regular string I get what looks correct to me:  
“2010-07-20 11:49:19”  
but saved in the entrycreated column it outputs as: “Sat Jan 01  
11:49:19 UTC 2000”

Can anyone help me with the correct code to save this datetime  
correctly pleased.

In case it’s relevant, on my dev machine I have sqlite. Eventually it  
will be on mysql.  
Thanks for any help,  
Steve

---

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 21, 2010, 4:38am UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/2 "2010-07-21T04:38:28Z")

</div>

Time.zone.parse might be your friend here:

> > date = Time.zone.parse(‘2010-07-20T11:49:19.000Z’)  
> > date = Time.zone.parse(‘2010-07-20T11:49:19.000Z’)  
> > =\> Tue, 20 Jul 2010 11:49:19 UTC +00:00
> 
> > date.to\_s(:db)  
> > date.to\_s(:db)  
> > =\> “2010-07-20 11:49:19”
> 
> > date.class.name  
> > date.class.name  
> > =\> “ActiveSupport::TimeWithZone”

Rails extensions to Time and Date classes can take a bit to work out  
in certain circumstances.

Hope that helps,  
Walter

---

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 21, 2010, 4:43am UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/3 "2010-07-21T04:43:09Z")

</div>

Oh, to finish up. Time.parse looks to be sufficient for what you are  
after, too.

> > date = Time.parse(‘2010-07-20T11:49:19.000Z’)  
> > date = Time.parse(‘2010-07-20T11:49:19.000Z’)  
> > =\> Tue Jul 20 11:49:19 UTC 2010
> 
> > date.class.name  
> > date.class.name  
> > =\> “Time”
> 
> > date.to\_s(:db)  
> > date.to\_s(:db)  
> > =\> “2010-07-20 11:49:19”

IIRC, the ActiveSupport::TimeWithZone class is better at handling  
pre-1900 dates than Rails extension of the Time class.

Cheers,  
Walter

On Wed, Jul 21, 2010 at 2:36 PM, Walter McGinnis

---

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 21, 2010, 9:10am UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/4 "2010-07-21T09:10:02Z")

</div>

On Jul 20, 9:31 pm, Steve R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> If I save that to a regular string I get what looks correct to me:  
> “2010-07-20 11:49:19”  
> but saved in the entrycreated column it outputs as: “Sat Jan 01  
> 11:49:19 UTC 2000”

Is you entrycreated column a time column ? Time columns only save a  
time of day (and ignore the date), what you want is a datetime column.

Fred

---

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 21, 2010, 4:18pm UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/5 "2010-07-21T16:18:03Z")

</div>

On 21 July, 08:08, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> Fred

Thanks! It was a simple as that. I’m sure I’d already checked my  
migration was creating a t.datetime column, but checking now it had  
t.time

---

<div class="post-metadata">

**Author:** ![cwd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/da6949/32.png) [@cwd](https://www.ruby-forum.com/u/cwd)\
**Post date:** [July 21, 2010, 9:05am UTC](https://www.ruby-forum.com/t/saving-xmlschema-datetime-into-sqlite-mysql/191931/6 "2010-07-21T09:05:21Z")

</div>

On Tue, Jul 20, 2010 at 1:31 PM, Steve R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> If I save that to a regular string I get what looks correct to me:  
> “2010-07-20 11:49:19”  
> but saved in the entrycreated column it outputs as: “Sat Jan 01  
> 11:49:19 UTC 2000”

How are you trying to save this? As a string?

> > bird = Bird.first  
> > =\> #\<Bird id: 2, common\_name: “hummingbird”, species: “hoverus  
> > buzzus”, migratory: true, created\_at: “2010-03-05 21:08:36”,  
> > updated\_at: “2010-05-28 14:40:52”, primary\_food: “nectar”,  
> > secondary\_food: “”\>  
> > whenever = bird.created\_at  
> > =\> Fri, 05 Mar 2010 21:08:36 UTC 00:00  
> > whenever.class  
> > =\> ActiveSupport::TimeWithZone

So not a _string_ here…

> > dt = DateTime.now  
> > =\> Tue, 20 Jul 2010 18:12:54 -0700  
> > bird.created\_at = dt  
> > =\> Tue, 20 Jul 2010 18:12:54 -0700  
> > bird.save  
> > =\> true

So maybe you want to convert your string to a DateTime object and  
save that? Just one possibility 🙂

–  
Hassan S. ------------------------ removed\_email\_address@domain.invalid  
twitter: @hassan