The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
1answer
12 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
1answer
19 views

Time series and gap filling

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
3
votes
1answer
120 views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
4
votes
2answers
60 views

List minutes instead of grouping by them

Looking how to list minutes 1 to 120 from a 2 hour interval timestamp field instead of getting each minute from both hours together. What I want is something like this: min COUNT(*) 1 ...
0
votes
0answers
25 views

compare time difference between current time with last data timestamp

I have a data server recording data every second. i need to setup a cron job that that runs a script that can give a reply if the now() is 24 hours more than the last data recorded. So that I can ...
1
vote
1answer
150 views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ...
0
votes
2answers
144 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 ...
1
vote
1answer
246 views

How to convert a unix time to PostgreSQL' Timstamp without timezome?

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30) I have some data in a table which is created like this: CREATE TABLE "CLOUDDATA" ( ...
3
votes
1answer
143 views

Timestamp on SSIS update on tsql

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be ...
0
votes
0answers
29 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
816 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 ...
2
votes
1answer
135 views

How to generate time in milliseconds in PostgreSQL stored procedure?

I have been reading the Postgres documentation about timestamps, but I am still a little bit confused. I need to store moments in times, measured in milliseconds, between the current time and ...
0
votes
1answer
164 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
203 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
143 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 ...
0
votes
2answers
2k views

How do I get the difference in minutes from 2 timestamp columns? [closed]

Can't seem to figure this out. Just need the difference in minutes between 2 oracle timestamp columns. END_TS START_TS ...
0
votes
1answer
485 views

Why are timestamps not always increasing with concurrent inserts?

I'm seeing some unexpected behavior with timestamp (rowversion) columns . I created a test table: create table Test ( Test_Key int identity(1,1) primary key clustered, Test_Value int, ...
0
votes
2answers
80 views

How to guess or recover a timestamp conversion in sqllite?

I am in the middle of tiny forensic effort =) I am trying to transfer transactions from Money IQ (IOS) to My Wallet+ (IOS) as both apps use sqlite. Money IQ uses a standard unixepoch timestamp. ...
2
votes
1answer
132 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
95 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
2k 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 ...
4
votes
2answers
124 views

Can a single SQL Server instance issue the same SYSDATETIME twice?

I am contemplating using a datetime2(7) as a clustered index. If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming ...
1
vote
1answer
552 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 ...
0
votes
1answer
91 views

Different time in different sessions

I am connecting to same db from 2 different machines using SQL Developer and querying a table which stores timestamp. But to my surpise the time value returned by query has a gap of one hour on ...
1
vote
2answers
459 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
106 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
764 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
295 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
131 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
651 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
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 ...
1
vote
1answer
49 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
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 ...
5
votes
3answers
587 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
0answers
90 views

database timestamp

What's the definition of timestamp? The timestamp is just for a transaction? ( no matter what actions in the transaction. The timestamp is always the time when the transaction happened.) OR the ...
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 ...
2
votes
3answers
2k 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 ( ...
4
votes
1answer
160 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
359 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
69 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
8k 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
312 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
5k 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
536 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
11k 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
159 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
449 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
1k 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. ...
0
votes
3answers
8k 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. ...
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 ...