1
vote
1answer
13 views

Select the last logged change before a specified time from a change list

I want to get the last change for all issues issue, that was made before a specified end time. I have 3 tables. projects +---+------------+ |ID |project_key | +---+------------+ |1 |bug_tracker | ...
0
votes
2answers
168 views

MySQL timestamp timezone handling

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be ...
0
votes
0answers
30 views

Output datetime from MySQL with offset

I'm currently writing to a datetime XML field from MySQL data. The MySQL field is populated with current_timestamp() but this only outputs - 2014-02-18 15:34:02 whereas I need something like ...
1
vote
1answer
865 views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
0
votes
1answer
183 views

How can I search a column in MySQL containing unix timestamps with a query referencing a different format?

I have a mysql table that contains something like: +-------------+-----------------+ | FOO1 | FOOTIME | +-------------+-----------------+ | blah | 1234567890000 | ...
1
vote
3answers
211 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
0
votes
1answer
149 views

Date of birth saving to MYSQL DB with timestamp or date column type [closed]

i am using MYSQL database and would like to know which of this is a good practice. way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as ...
2
votes
1answer
135 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
0
votes
1answer
96 views

Are triggers bad for created/timestamp columns?

We are in need of some better auditing on some of our MySQL tables. Specifically, we'd like to store when the row was inserted and when it was last updated. Originally, I thought DEFAULT ...
1
vote
1answer
3k views

Why does mysql adds a default timestamp on insert/update even if I did not specify anything?

I have a table that is as follows: CREATE TABLE SomeTable ( id int unsigned NOT NULL AUTO_INCREMENT, status enum('broken','repaired') NOT NULL, from datetime NOT NULL, until ...
1
vote
1answer
562 views

MySQL cluster 5.6 doesn't convert invalid TIMESTAMP value to '0000-00-00 00:00:00'?

According to the document: Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'). but it doesn't ...
1
vote
2answers
476 views

INT or TIMESTAMP for the change time column in a logging table?

We're having an argument over what to use for storing change date in a new log table for our site. One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime ...
0
votes
1answer
110 views

MySQL system date change impact

I can't find an answer for this problem. If I have in my database data of datatypes like date and timestamp. Can the change of system date affect this saved data ? I mean, can these existing ...
1
vote
1answer
788 views

MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table: CREATE TABLE `judgements` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, ...
2
votes
1answer
297 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
0
votes
1answer
1k views

Should I disable triggers, timestamps in a slave in mysql?

We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
3
votes
1answer
1k views

What happens when changing column type DATETIME to TIMESTAMP in MySQL?

I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines. Is it possible? Is it slow? Will I lose the old information or MySQL can convert this ...
4
votes
1answer
153 views

How can I improve this DB query? it takes too much time

The query is this: $today = $date->getTimestamp(); SELECT t.*, u.screen_name, u.profile_image_url FROM table AS t LEFT JOIN users AS u ON t.user_id=u.id_str WHERE ...
4
votes
1answer
163 views

MySQL year 2038 vulnerable

I was studying Drupal and asked this question: http://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446 I decided to check, how the CMS ...
0
votes
2answers
542 views

Dates changed to 0000-00-00

Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME ...
5
votes
2answers
12k views

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | ...
1
vote
1answer
4k views

How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time). As I understand it, the DATETIME is set to ...
2
votes
3answers
1k views

Finding five-minute interval prior to current time

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as ...
5
votes
2answers
4k views

Storing time with milliseconds in database

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds). I was originally going to ...
2
votes
3answers
2k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
2
votes
1answer
395 views

MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
5
votes
2answers
1k views

Best way to handle dates prior to 1000 A.D. in MySQL?

I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000. Is there a way that would be more convenient than either ...
1
vote
2answers
2k views

How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

I want to set `addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed: mysql> alter table mytable change c1 ...