Tagged Questions

The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
1answer
26 views

Breaking down by minutes in between two dates

In MySQL, I've got a data model with a "visit" table that has start and end datetimes. How can I get a breakdown of the number of visits that are active per minute? e.g. | visit_id | start | end ...
0
votes
3answers
31 views

How do I unit test queries which are based on dates from now?

I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test. For ...
0
votes
1answer
41 views

Select Statement to generate result, for Group by per second and link with Table

i tried to search in existing queries but didnt find any related thread. i would like to run a query in user table (for example), and like to get output result on per second bases. and give me zero ...
1
vote
3answers
34 views

Get seconds of day in Postgres

In Java we use Joda-Time to get the seconds of a day as an int value (despite the date): date: 10-10-2014 00:00:30 -> second 30 of day date: 11-10-2014 00:01:30 -> second 90 of day date: ...
1
vote
1answer
53 views

First date in a week, not exceed from the month, start from specific day

I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried : SET @date:='2014-10-03'; SELECT IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) ...
1
vote
1answer
31 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
2
votes
1answer
122 views

How to find number of days since beginning of fiscal month?

This relates to my question How to find XXth day of previous month in SQL server? The fiscal month starts on 26th of each month. I need to find out from any date: The date of the start of the ...
3
votes
1answer
243 views

How to find XXth day of previous month in SQL server?

I have 2 parameters, an input date (DATETIME) input day of month (TINYINT) If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the ...
1
vote
2answers
302 views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): ...
0
votes
1answer
77 views

Case always returns null?

I have a subquery that gets the DATEDIFF of two dates. However the first case always returns null yet the actual sub query returns dates?! CASE WHEN [date1] > [date2] THEN ...
1
vote
1answer
119 views

How does Sybase store datatime information?

We have a Sybase database that currently stores datetime values in some of the tables. If we move that server with the Sybase database to another country which is in another time zone, will the ...
1
vote
0answers
148 views

Why does this MySQL date_sub statement return wrong values?

I had a complex statement that was returning strange values and I traced it to the following line: SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE) ORDER BY ...
2
votes
1answer
66 views

Optimal table design for a medical center rental service and recurrent shifts

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine. A medical center has 14-16 offices/rooms ...
6
votes
4answers
180 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
0
votes
0answers
45 views

Cohort analisis for gamers activity

I want to do some cohort analysis of gamer activity in my games. By cohort I mean some set of users who registered (or had first activity) on some particular date. For example: users registered on 16 ...
0
votes
1answer
164 views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT ...
2
votes
2answers
164 views

Datetime column vs Date + Time columns

I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second. We are using SQL-Server 2012 Enterprise Edition. I have a question regarding breaking up a ...
0
votes
1answer
109 views

selecting records that are 3 days from expiring datetime

I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp. Here is what I have ...
1
vote
1answer
4k views

Understanding datetime formats in SQL Server

I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening: I query a table that contains a smalldatetime column. This smalldatetime is always ...
2
votes
2answers
247 views

Select different time

I looked on the internet and I found your post on the stackexchange. I will try to explain in more detail what we really need I do not know well to write English It is a system for airporttaxi id ...
1
vote
1answer
70 views

merge rows based on column

I have attendance_log table : +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
2answers
56 views

Query to test and return timed lockout in MySQL

I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level). My query looks like ...
0
votes
1answer
70 views

Subtracting dates give me error in Postgresql

I use age to get the interval between to date fields. Their type is timestamp. My code is as below: select age(timestamp MySecondDateField, timestamp MyFirstDateField) The data in my field ...
1
vote
3answers
379 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
52 views

How can I efficiently search for all records from a given month, or a given year?

We have a couple queries that involve datetime fields. In particular, we are checking for things like SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1 SELECT field1, field2, ...
4
votes
4answers
3k views

Select COUNT of days between two dates except weekends

I am trying to get the number of days between two different dates, except the weekends. I don't know how to achieve this result.
2
votes
1answer
254 views

MySQL: What is the most optimized way to use datetime type field in query

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery: select * from jos_content c where c.visible = 1 ...
2
votes
1answer
78 views

DATETIME2FROMPARTS alternatives for SQL Server 2008

I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly. Usually this works on a SQL Server 2012 machine: DATETIME2FROMPARTS( ...
0
votes
1answer
350 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
1answer
2k views

SQL Query with multiple conditions on date

I want to write a query with condition on dates for below table and sometimes it requires great thinking. StartDtm StopDtm 2013-11-03 00:00:00.000 NULL ...
0
votes
1answer
149 views

How to create a trigger that update the date and time field and modify of the row

I want to create trigger that will update the record. The situation is like: I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status ...
5
votes
1answer
1k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
1
vote
2answers
298 views

SQL Server and SharePoint time problem

I have created a SharePoint application which has a specific time zone. The Windows server where SQL Server and SharePoint are installed are using same time zone. When I create a new item in list ...
0
votes
1answer
189 views

How can I speed up a DateTime comparison query in MySQL?

I am performing the following query on a MySQL table with around a million records ( ~350MB ). It takes roughly 1.5 seconds every time. If I remove the OR last_updated > last_geocoded comparison, ...
8
votes
2answers
649 views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
1
vote
1answer
766 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 ...
3
votes
1answer
930 views

partitioning by datetime vs date vs int performance

I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server. We know we want daily partitions. The data has a natural DATETIME field in it, so my gut ...
2
votes
1answer
2k views

MySQL query to evaluate ranges in date and time elements of a datetime column separately

TABLE EventID, EventTime (DATETIME) 1 2013-09-29 23:55:00.0 2 2013-10-01 00:05:00.0 3 2013-09-29 23:55:00.0 4 2013-09-29 23:45:00.0 5 2013-10-02 23:05:00.0 6 ...
1
vote
1answer
98 views

Constructing datetime from 2 fields: one integer and a datetime

I am looking for the most efficient way to parse 2 values; the first is stored as a datetime field with (hh:mm:ss.fff all being 0) and the second time field stored as an int with the format: ...
3
votes
1answer
11k views

MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

I'm using MySQL 5.6 and I have a program that runs the following SQL statement: UPDATE m_table SET s_time = '2013-08-25T17:00:00+00:00' WHERE id = '123' against my database. Unforutnately, I get the ...
1
vote
1answer
407 views

Advice needed for Querying DateTimeOffset data by end users

We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time ...
0
votes
1answer
565 views

Extract time portion of Datetime2(7) in hhmmssfff format

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this: DECLARE @mdate AS DATETIME2(7) SET @mdate = '2012-03-15 10:13:27.5437431' SELECT ...
2
votes
1answer
317 views

Grouping over multiple data dimensions (pivot)

I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise. The two dimensions ...
4
votes
1answer
357 views

Understanding MS SQL Server Date Types

Consider the following: declare @dt datetime, @dt2 datetime2, @d date set @dt = '2013-01-01' set @dt2 = '2013-01-01' set @d = '2013-01-01' select convert(varbinary, @dt) as dt, ...
0
votes
1answer
137 views

PostgreSQL - Date added row metadata

Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with ...
1
vote
2answers
116 views

Finding rows for a specified date range

I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate: CREATE TABLE `pupilgroupteacher` ( `id` int(10) unsigned NOT NULL ...
1
vote
2answers
79 views

Get rows in a range of time

I have a table with two columns that stores TIME type values. This is the CREATE statement: CREATE TABLE `sdriving_turno` ( `idturno` bigint(20) NOT NULL AUTO_INCREMENT, `idempresa` int(10) ...
3
votes
5answers
1k views

Combine datetime + string to make a proper datetime

I need to take a DATETIME column (Column A) that contains only a date, then add a VARCHAR(5) column (Column B) which contains a 24-hour representation of a time (i.e. 1330) and combine them to get a ...
-3
votes
2answers
524 views

How/Where to get date/time/calendar data to store in a table? [closed]

I cannot recall where I got the data before so I'm asking here: Where can I get or how can I generate standard date/time/calendar data? The table would consist of columns such as: main_date -- ...
0
votes
1answer
139 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 ...