1

Im trying to execute the following query on my mysql database:

INSERT INTO `adabtc` (`time`, `open`, `high`, `low`, `close`, `volume`) VALUES
('2018-03-30 02:00:00','0.00002002', '0.00002031', '0.00002000', '0.00002011', '105.88731690')

and I get the following error:

1062 - Duplicate entry '2018-03-30 03:00:00' for key 'PRIMARY'

Notice the date in the query is different from the duplicate one.

Create Table:

CREATE TABLE `adabtc` (
 `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `open` double NOT NULL,
 `high` double NOT NULL,
 `low` double NOT NULL,
 `close` double NOT NULL,
 `volume` double NOT NULL,
 PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

also running the query:

SELECT * FROM `adabtc` WHERE time BETWEEN '2018-03-30 01:00:00' AND '2018-03-30 03:00:00'

gives me the following outcome:

2018-03-30 01:00:00 0.00001983 0.00002003 0.00001968 0.00002001 110.25848914

2018-03-30 03:00:00 0.00002002 0.00002031 0.00002 0.00002011 105.8873169

SOLVED: changed type from timestamp to datetime and the issue was resolved. Thanks @RaymondNijland

Community
  • 1
  • 1
marsel adawi
  • 11
  • 2
  • 2
    Please [edit] your question to include the `CREATE TABLE` statement of your `adabtc` table. Also explain why you have set your `time` column as the primary key and why you want to add a new row with the same `time` value. – Progman Nov 23 '19 at 12:47
  • @Progman the date in the query is different from the one in the error – marsel adawi Nov 23 '19 at 12:52
  • 1
    Are there any triggers defined? – forpas Nov 23 '19 at 12:55
  • anyhow `SHOW CREATE TABLE adabtc` – Raymond Nijland Nov 23 '19 at 12:58
  • [edit](https://stackoverflow.com/questions/59007752/mysql-query-duplicate-with-a-different-primary-key-value) the question please.. – Raymond Nijland Nov 23 '19 at 13:00
  • *"Notice the date in the query is different from the duplicate one."* I can **not** [reproduce](https://www.db-fiddle.com/f/4cUXLFD8gQBB9HvrmKSs6g/0) this problem, running this insert twice gives `Duplicate entry '2018-03-30 02:00:00' for key 'PRIMARY'` like expected.. But i noticed you used `timestamp` datatype so this feels a timezone setting difference alter it to a `DATETIME` datatype instead to store as UTC. – Raymond Nijland Nov 23 '19 at 13:05
  • Possible duplicate of [Should MySQL have its timezone set to UTC?](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc) – Raymond Nijland Nov 23 '19 at 13:12
  • That duplication which explains more or less everthing around UTC and timezones in MySQL, pretty sure it is usefull – Raymond Nijland Nov 23 '19 at 13:12
  • yes thanks, changing to datetime solved it. – marsel adawi Nov 23 '19 at 13:15

1 Answers1

1

I am pretty sure this is a time zone issue. As the documentation explains:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

Assuming that your server is one hour off from UTC, that would explain the difference between the input value and the error message.

You need to take this offset into account. One solution is to provide the timestamps in UTC. A better solution is to use datetime, which is timezone independent.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • All the timestamps are in fact, in UTC. also running the query to insert an hour before duplicates with the correct value, so it must me something else – marsel adawi Nov 23 '19 at 13:07
  • *"All the timestamps are in fact, in UTC"* @marseladawi i don't think that you understood what Gordon meant with this... Read [Should MySQL have its timezone set to UTC?](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc) which explains more or less everthing around UTC and zonetimes. – Raymond Nijland Nov 23 '19 at 13:10
  • @marseladawi . . . The issue is that MySQL converts a string without a timezone to UTC based on the server timezone. You don't want this to be happening. You either need to be explicit about the timezone in the constant. Or, use the data type you really want, which is `datetime`, rather than `timestamp`. – Gordon Linoff Nov 23 '19 at 14:17