New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.
Already on GitHub?
Sign in
to your account
Closed
lnreeder opened this issue
Jan 17, 2016
· 17 comments
Assignees
Labels
bug
Something isn’t working
Comments
The https://www.sqlite.org/datatype3.html indicates date/time functions in SQLite can store dates as: «TEXT as ISO8601 strings («YYYY-MM-DD HH:MM:SS.SSS»). «
However, when storing an ISO 8601 string like 2016-01-01 00:00:00, then attempting to use sqlite-jdbc to retrieve as a java Timestamp, ResultSet.getTimestamp returns an error:
testSelectIso8601Timestamp(org.sqlite.ResultSetTest) Time elapsed: 0.003 sec <<< ERROR!
java.sql.SQLException: Error parsing time stamp
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:532)
at org.sqlite.ResultSetTest.testSelectIso8601Timestamp(ResultSetTest.java:156)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
Caused by: java.text.ParseException: Unparseable date: «2016-01-01 00:00:00» does not match (p{Nd}++)Q-E(p{Nd}++)Q-E(p{Nd}++)Q E(p{Nd}++)Q:E(p{Nd}++)Q:E(p{Nd}++)Q.E(p{Nd}++)
at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:529)
… 32 more
Reproduction test class is attached. See test «testSelectIso8601Timestamp»
ResultSetTest.java.txt
You have two options here
- Use the expected format:
2016-01-01 00:00:00.000
- Set a different value for the pragma
date_string_format
:
SQLiteConfig sqLiteConfig = new SQLiteConfig();
Properties properties = sqLiteConfig.toProperties();
properties.setProperty(Pragma.DATE_STRING_FORMAT.pragmaName, "yyyy-MM-dd HH:mm:ss");
conn = DriverManager.getConnection("jdbc:sqlite:", properties);
I don’t know if this is a bug. One could think the value should be converted from 2016-01-01 00:00:00
to 2016-01-01 00:00:00.000
before saving (because of the date_string_format
) but I have no idea how this would harm the amount of disk space which is necessary to save this additional information.
The bad thing here is that you can save a value which you can’t load anymore. This is because the pattern which is used to parse the timestamp from the database is determined by the date_string_format
which doesn’t fit to the original saved value.
This also happens when using current_timestamp
.
Example:
create table users ( id integer primary key autoincrement not null, email varchar(255) unique not null, password char(60) not null, created_at timestamp default current_timestamp not null ); insert into users (email, password) values ('x@x.com', 'bcrypted password'); select * from users order by created_at; -- rs.getTimestamp(4) throws
Caused by: java.sql.SQLException: Error parsing time stamp
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:526)
at be.bendem.sqlstreams.impl.SqlBindings.lambda$map$1(SqlBindings.java:38)
at be.bendem.sqlstreams.impl.Wrap.get(Wrap.java:27)
... 56 more
Caused by: java.text.ParseException: Unparseable date: "2016-04-24 10:39:36" does not match (p{Nd}++)Q-E(p{Nd}++)Q-E(p{Nd}++)Q E(p{Nd}++)Q:E(p{Nd}++)Q:E(p{Nd}++)Q.E(p{Nd}++)
at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:523)
... 58 more
EDIT: Same problem happens with date
and current_date
instead of timestamp
and current_timestamp
:
Caused by: java.sql.SQLException: Error parsing date
at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:290)
at be.bendem.sqlstreams.impl.SqlBindings.lambda$map$1(SqlBindings.java:38)
at be.bendem.sqlstreams.impl.Wrap.get(Wrap.java:27)
... 56 more
Caused by: java.text.ParseException: Unparseable date: "2016-04-24" does not match (p{Nd}++)Q-E(p{Nd}++)Q-E(p{Nd}++)Q E(p{Nd}++)Q:E(p{Nd}++)Q:E(p{Nd}++)Q.E(p{Nd}++)
at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
at org.sqlite.jdbc3.JDBC3ResultSet.getDate(JDBC3ResultSet.java:287)
... 58 more
Running into this again, it makes using dates with this driver such a pain. Would be nice if it was fixed.
I know it might sounds crazy, but I kinda expect this driver to be able to retrieve the dates and timestamp it generated.
You have two options here
- Use the expected format: 2016-01-01 00:00:00.000
- Set a different value for the pragma date_string_format:
Actually there is a third option in a form of a pull request #33 that I’m using. I just wonder why it never got applied.
xerial
added
the
bug
Something isn’t working
label
Jan 10, 2017
Ran into this just today.
Seconding @bendem here, would love to see this fixed and the driver to be able to parse all possible sqlite3 dates.
@TuomasKiviaho Some test cases are failing in #33, but it never get fixed by the author.
@xerial There was an explanation in the ticket that the the test is not conforming with standard so by fixing the following method, I suppose it would start working. There is also a quite thorough explanation what is wrong with it.
@Test public void dateTimeTest() throws Exception { Connection conn = getConnection(); conn.createStatement().execute("create table sample (start_time datetime)"); Date now = new Date(); String date = FastDateFormat.getInstance(SQLiteConfig.DEFAULT_DATE_STRING_FORMAT).format(now); conn.createStatement().execute("insert into sample values(" + now.getTime() + ")"); conn.createStatement().execute("insert into sample values('" + date + "')"); ResultSet rs = conn.createStatement().executeQuery("select * from sample"); assertTrue(rs.next()); assertEquals(now, rs.getDate(1)); assertTrue(rs.next()); assertEquals(now, rs.getDate(1)); PreparedStatement stmt = conn.prepareStatement("insert into sample values(?)"); stmt.setDate(1, new java.sql.Date(now.getTime())); }
Hello,
I closed issue #24, because I thought the issue had been address and I did not have additional
time to review the failing tests in pull #33.
After testing today, 3.19.3, and reviewing this issue, it is still present. The main problem appears to
be on the storing of java.sql.Time, Date, and Timestamp in a java.util.Date numeric value in the
database. That approach in my opinion is perhaps incorrect. It is fine for a Timestamp, but TIME, DATE, DATETIME, have the formats (YYYY-mm-dd), (HH:mm:ss), and (YYYY-mm-dd HH:mm:ss), String literals.
As pointed out a change now will break all databases which are storing these types as numeric
if changed to strings. The orginal pull #33 was a patch. I never had or do now have the knowledge
of how DBs store these types typically, this seems to be an architecture issue for the driver in
order to be properly fixed.
I have created an additional test to today that shows an error with the SQL Statement execution
for the problem, Prepare Statements work, but again the wrong data numeric, seems present
instead of strings being stored.
https://dandymadeproductions.com/temp/SQLite_JDBC_Temporal.java
danap.
Ajqvue Project Manager.
As pointed out a change now will break all databases which are incorrectly storing the thesetemporal values, beside Timestamp. I believe these other SQL types should be stored asthere correct represented STRINGS to make the SQLite Database compatible with other DBsand tools in accessing the data.
This doesn’t have to be the case, the fix would be to add support for a *new* format. That change shouldn’t affect the parsing of existing dates and times.
This doesn’t have to be the case, the fix would be to add support
for a new format. That change shouldn’t affect the parsing
of existing dates and times.
bendem
I would agree. I would assume that the storing of these types,
TIME, DATE, DATETIME, and TIMESTAMP in Long values was chosen
for a purpose, efficiency, limit space in the database, etc…
If that would be to be maintained then INSERTS as demonstrated
in my test case, strings, should still function properly when
the data is retrieved with either getTime(), getDate(), etc,
but also getString(). Also pointed out at start of this issue.
Xerial,
The other reason I did not review the test cases that were
breaking with pull #33 is because I did not get a clear confirmation
that the pull would be even accepted. I had already spent a
week reviewing the code and putting together a patch that might
not have been the proper approach/solution.
I’m trying too do a complete review of my project’s support of
databases, and I use this sqlite-jdbc driver. At this point I
really think my test case points to a serious deficiency in this
driver that has still not been fixed. I hate to think that I
would again spend time, weeks understanding the code and putting
a patch together for this project without better communication.
#33
danap.
Someone try to resolve problem? Or maybe have alternative options for getting and saving date in database via SQLite JDBC.
It is still a valid issue.
I could accept the fact that we should use the proper timestamp format…
BUT
when using built in functions like current_timestamp
or datetime('now')
and I could not read it back without «hacking» is not something what could be considered as a solution/answer.
Could anyone sum up what is really missing to fix it?
I am considering to be involved and help in any implementation concerns.
Implementation point of view I could imagine a solution like commons lang DateUtil.parseDate.
dckc
added a commit
to kumc-bmi/heron-admin
that referenced
this issue
May 2, 2019
It didn't work, but this was an attempt to deal with... xerial/sqlite-jdbc#88 JDBC driver unable to parse ISO 8601 dates --HG-- branch : jvm_deploy_254
Unparseable date: «1962-02-18 00:00:00» does not match (p{Nd}++)Q-E(p{Nd}++)Q-E(p{Nd}++)Q E(p{Nd}++)Q:E(p{Nd}++)Q:E(p{Nd}++)Q.E(p{Nd}++)
(milliseconds are missing)
+1 for getting this fixed, somehow.
Totally agree with all the previous comments.
It is dumb that the SQLite Driver is unable to parse date strings created by SQLite.
I think I found the issue: the regex used to parse the date strings only matches if the string contains millisecond information. Apparently those created by CURRENT_TIMESTAMP
do not include millisecond information, which was causing the regex to not match the date string, which was preventing the date string from getting parsed as a Date.
I submitted a pull request to try and resolve the issue. Basically I added millisecond information to the date string if it did not already have it. The «millisecond information» I added was just zeros, which clearly does not change the date represented by the date string, so this seems like a safe solution to me.
Hopefully someone accepts my pull request and this bug can finally be resolved.
The fix has been merged! If you are still experiencing this issue try updating your version of sqlite-jdbc
Hi David,
Now I have to fix my workaround.
Thanks for your efforts. +1
I’ll release 3.36.0.1 today.
This was referenced
Jun 30, 2021
Labels
bug
Something isn’t working
Even though notification
is apparently a supported element (according to the Firebase web docs), the only way I could get rid of the exception was to remove it entirely, and use the data
section only, and then in my app create a notification (rather than letting firebase do the notification).
I used this site to work out how to raise the notifications: https://www.androidhive.info/2012/10/android-push-notifications-using-google-cloud-messaging-gcm-php-and-mysql/
My notification now looks like the following:
$fields = array("to" => "<valid-token>",
"data" => array("data"=>
array(
"message"=>"This is some data",
"title"=>"This is the title",
"is_background"=>false,
"payload"=>array("my-data-item"=>"my-data-value"),
"timestamp"=>date('Y-m-d G:i:s')
)
)
);
...
<curl stuff here>
...
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($fields));
My onMessageReceived
looks like this:
@Override
public void onMessageReceived(RemoteMessage remoteMessage) {
Log.d(TAG, "From: " + remoteMessage.getFrom());
// Check if message contains a data payload.
if (remoteMessage.getData().size() > 0) {
Log.e(TAG, "Data Payload: " + remoteMessage.getData().toString());
try {
JSONObject json = new JSONObject(remoteMessage.getData().toString());
handleDataMessage(json);
} catch (Exception e) {
Log.e(TAG, "Exception: " + e.getMessage());
}
}
}
which calls handleDataMessage
which looks like this:
private void handleDataMessage(JSONObject json) {
Log.e(TAG, "push json: " + json.toString());
try {
JSONObject data = json.getJSONObject("data");
String title = data.getString("title");
String message = data.getString("message");
boolean isBackground = data.getBoolean("is_background");
String timestamp = data.getString("timestamp");
JSONObject payload = data.getJSONObject("payload");
// play notification sound
NotificationUtils notificationUtils = new NotificationUtils(getApplicationContext());
notificationUtils.playNotificationSound();
if (!NotificationUtils.isBackgroundRunning(getApplicationContext())) {
// app is in foreground, broadcast the push message
Intent pushNotification = new Intent(ntcAppManager.PUSH_NOTIFICATION);
pushNotification.putExtra("message", message);
LocalBroadcastManager.getInstance(this).sendBroadcast(pushNotification);
} else {
// app is in background, show the notification in notification tray
Intent resultIntent = new Intent(getApplicationContext(), MainActivity.class);
resultIntent.putExtra("message", message);
showNotificationMessage(getApplicationContext(), title, message, timestamp, resultIntent);
}
} catch (JSONException e) {
Log.e(TAG, "Json Exception: " + e.getMessage());
} catch (Exception e) {
Log.e(TAG, "Exception: " + e.getMessage());
}
}
this then calls showNotificationMessage
/**
* Showing notification with text only
*/
private void showNotificationMessage(Context context, String title, String message, String timeStamp, Intent intent) {
notificationUtils = new NotificationUtils(context);
intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK | Intent.FLAG_ACTIVITY_CLEAR_TASK);
notificationUtils.showNotificationMessage(title, message, timeStamp, intent);
}
And subsequently notificationUtils.showNotificationMessage
public void showNotificationMessage(String title, String message, String timeStamp, Intent intent) {
showNotificationMessage(title, message, timeStamp, intent, null);
}
public void showNotificationMessage(final String title, final String message, final String timeStamp, Intent intent, String imageUrl) {
// Check for empty push message
if (TextUtils.isEmpty(message))
return;
// notification icon
final int icon = R.mipmap.ic_launcher;
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP | Intent.FLAG_ACTIVITY_SINGLE_TOP);
final PendingIntent resultPendingIntent =
PendingIntent.getActivity(
mContext,
0,
intent,
PendingIntent.FLAG_CANCEL_CURRENT
);
final NotificationCompat.Builder mBuilder = new NotificationCompat.Builder(
mContext);
final Uri alarmSound = Uri.parse(ContentResolver.SCHEME_ANDROID_RESOURCE
+ "://" + mContext.getPackageName() + "/raw/notification");
showSmallNotification(mBuilder, icon, title, message, timeStamp, resultPendingIntent, alarmSound);
playNotificationSound();
}
private void showSmallNotification(NotificationCompat.Builder mBuilder, int icon, String title, String message, String timeStamp, PendingIntent resultPendingIntent, Uri alarmSound) {
NotificationCompat.InboxStyle inboxStyle = new NotificationCompat.InboxStyle();
inboxStyle.addLine(message);
Notification notification;
notification = mBuilder.setSmallIcon(icon).setTicker(title).setWhen(0)
.setAutoCancel(true)
.setContentTitle(title)
.setContentIntent(resultPendingIntent)
.setSound(alarmSound)
.setStyle(inboxStyle)
.setWhen(getTimeMilliSec(timeStamp))
.setSmallIcon(R.mipmap.ic_launcher)
.setLargeIcon(BitmapFactory.decodeResource(mContext.getResources(), icon))
.setContentText(message)
.build();
NotificationManager notificationManager = (NotificationManager) mContext.getSystemService(Context.NOTIFICATION_SERVICE);
notificationManager.notify(ntcAppManager.NOTIFICATION_ID, notification);
}
More detail in the link above, and it’s a lot of handling but at least the exception’s gone and I’m in control of the notifications.
0 / 0 / 0 Регистрация: 16.02.2016 Сообщений: 101 |
|
1 |
|
06.04.2018, 02:58. Показов 1094. Ответов 9
Заметил такую вещь что если при создании таблицы делаю поле DEFAULT CURRENT_TIMESTAMP я не могу его считать возникает Date parse exception а если оставляю и при помощи sqllite expert ввожу то считываю нормально без исключений почему так
__________________
0 |
4733 / 3938 / 997 Регистрация: 29.08.2013 Сообщений: 25,248 Записей в блоге: 3 |
|
06.04.2018, 06:56 |
2 |
потому что запятые в предложении нужно расставлять — ничего не понятно
0 |
0 / 0 / 0 Регистрация: 16.02.2016 Сообщений: 101 |
|
06.04.2018, 17:18 [ТС] |
3 |
не получается считать поле типа timestamp с resultset после использовании для его инициализации CURRENT_TIMESTAMP.
0 |
4733 / 3938 / 997 Регистрация: 29.08.2013 Сообщений: 25,248 Записей в блоге: 3 |
|
06.04.2018, 17:47 |
4 |
кто его знает как ты его считываешь
0 |
0 / 0 / 0 Регистрация: 16.02.2016 Сообщений: 101 |
|
07.04.2018, 14:57 [ТС] |
5 |
всм кто его знает, я же написал методом getTimestamp.
0 |
4733 / 3938 / 997 Регистрация: 29.08.2013 Сообщений: 25,248 Записей в блоге: 3 |
|
07.04.2018, 17:32 |
6 |
я же написал методом getTimestamp. какого языка?
Я же задал вполне конкретный вопрос вопрос про формат? никак а вообще показывай код, из твоих якобы вопросов вообще непонятно что ты делаешь
0 |
Deczy 0 / 0 / 0 Регистрация: 16.02.2016 Сообщений: 101 |
||||
07.04.2018, 17:58 [ТС] |
7 |
|||
0 |
4733 / 3938 / 997 Регистрация: 29.08.2013 Сообщений: 25,248 Записей в блоге: 3 |
|
07.04.2018, 18:33 |
8 |
это ветка по базам данных, значит тут язык это SQL
0 |
Deczy 0 / 0 / 0 Регистрация: 16.02.2016 Сообщений: 101 |
||||
07.04.2018, 20:02 [ТС] |
9 |
|||
Мне кажется вы не так поняли мой вопрос.
0 |
4733 / 3938 / 997 Регистрация: 29.08.2013 Сообщений: 25,248 Записей в блоге: 3 |
|
07.04.2018, 20:13 |
10 |
все равно не понимаю проблему
0 |