The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
2answers
1k 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
282 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
2k 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
406 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 ...
1
vote
2answers
208 views

Group Database Entries by time difference

I have a large postgreSQL database with log data. All this data has timestamps and I want to group consecutive rows where the difference between the timestamps is less then 1500 miliseconds for ...
4
votes
2answers
992 views

Alternative to sequence and timestamp: uniquely ordering records in time

Oracle 11gR2 Exadata I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
0
votes
1answer
2k 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 ...
1
vote
1answer
59 views

Script to get duration

I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
3
votes
1answer
2k 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 ...
5
votes
3answers
1k views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ...
0
votes
1answer
116 views

Database timestamp

What's the definition of timestamp? The timestamp is just for a transaction? (no matter what actions are in the transaction. The timestamp is always the time when the transaction happened). OR the ...
4
votes
1answer
162 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 ...
2
votes
3answers
3k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
6
votes
1answer
471 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 ...
3
votes
1answer
602 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
1
vote
1answer
71 views

Deadline rule date input after now

Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date. Currently I'm attempting this with a rule. CREATE OR REPLACE RULE "DeadlineInput" AS ON INSERT ...
2
votes
2answers
16k views

Comparing date and timestamp variables in a where clause in a procedure

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
1
vote
1answer
443 views

TimeZone parameter in postgresql.conf

postgresql documentation says: All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter ...
4
votes
2answers
8k views

How can I time-stamp data in MongoDB?

I'm responsible for writing an app to juggle a huge amount of client and business intelligence information. This information involves records for various businesses and metrics like revenue, their web ...
0
votes
2answers
782 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 ...
9
votes
2answers
23k 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
188 views

timestamp based concurrency

I am wondering if someone could help me out with a question I have about time stamp based concurrency. I found the following practice problem on a website It basically asks which of the following ...
2
votes
0answers
614 views

Logic to convert a UTC date-time to a specific time zone

I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred. I have a DIM ...
1
vote
2answers
2k views

LEFT JOIN conversion to INNER JOIN, can't change FROM clause

I have 2 tables: BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation. These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date. ...
1
vote
4answers
19k views

Convert a Unix timestamp to a DATETIME in a View

I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out. ...
2
votes
1answer
7k 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
1answer
8k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
3answers
2k 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 ...
3
votes
2answers
853 views

sql server management studio 2008 sucks up with memory yielding .net runtime failure

PROBLEM sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, ...
6
votes
2answers
6k 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
3k 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
433 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 ...
6
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 ...
6
votes
2answers
1k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ...
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 ...
33
votes
2answers
40k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
8
votes
2answers
29k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM ...