Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Java Development

Reply
Regular Contributor
Posts: 94
Registered: ‎02-27-2010
My Device: rien
My Carrier: etudiant

save date in database

hi How can I save a field of type DATE in the SQLite database?

Developer
Posts: 1,305
Registered: ‎01-21-2009
My Device: Not Specified

Re: save date in database

[ Edited ]

SQLite doesn't have a DATE type. You have three options for storing dates in SQLite:

  1. ISO 8601 text strings (good back to year 0, nore or less)
  2. 8-byte (long) integers (the Unix epoch: milliseconds since the start of the year 1970)
  3. reals representing the Julian date in the proleptic Gregorian calendar (good for going back over 6,700 years, but with no time of day information)

See the SQLite data types documentation for how to deal with dates in SQL queries, including lots of functions for manipulating SQLite date values.

 

For Java Date or Calendar objects, the easiest approach, I think, is to use Date.getTime/setTime to convert between Date and long values. Of course, if you need to deal with dates before 1970, you need to use one of the other representations. [misinformation removed following Peter's posting below]




Solved? click "Accept as solution". Helpful? give kudos by clicking on the star.
Developer
Posts: 1,055
Registered: ‎01-25-2009
My Device: BlackBerry Bold 9650/BlackBerry Torch 9800

Re: save date in database

[ Edited ]

Too late..

 

Follow Ted's suggestions.

Jerome Carty - Follow jcarty on Twitter@jcarty | #webworks-dev / #BlackberryDev on irc.freenode.net | My Apps
Click "Accept as Solution" if post solved your original issue.. Give like/thumbs up if you feel post is helpful
Developer
Posts: 19,636
Registered: ‎07-14-2008
My Device: Not Specified

Re: save date in database

Note that setting a Date to a negative value does actually represent a date before 1/1/1970.  For example, setting the date to -1000, is one second to midnight on 31 December 1969. 

Developer
Posts: 1,305
Registered: ‎01-21-2009
My Device: Not Specified

Re: save date in database

Peter's right about negative integers for dates. An 8-byte integer (a Java long value) can go back well before the start of the known universe, "and so suffices" (to quote the Wikipedia article on Unix time Smiley Happy).




Solved? click "Accept as solution". Helpful? give kudos by clicking on the star.
Highlighted
Developer
Posts: 1,305
Registered: ‎01-21-2009
My Device: Not Specified

Re: save date in database

Just a quick follow-up about using negative integers for Date objects. It works in Java and in SQLite (because it doesn't really know about dates). But if you interact with a server that is using MySQL, this could bite you. MySQL treats Unix epoch numbers as unsigned integers. Trying to convert a negative epoch value to a MySQL timestamp or date/time value doesn't work to well in MySQL. Caveat emptor.

 

(I knew that I hadn't just imagined that pre-1970 problem.)




Solved? click "Accept as solution". Helpful? give kudos by clicking on the star.