datetime-generation

How to create a list of seconds in MySQL

I have the following query: SELECT timestamp, COUNT(*) FROM table GROUP BY timestamp But some timestamps do not show up because there is no data. Here's an example 1:00:00 | 3 1:00:02 | 17 1:00:03 | 2 Notice that 1:00:01 is missing. Is there a way to make the 1:00:01 | 0 appear in the result? ...

MYSQL select count of rows that fall in a month for every month of the year.

With the table: id date_from date_to --------------------------- 1 2010-01-01 2010-03-01 2 2010-02-07 2010-05-01 3 2010-07-05 2010-07-10 I am trying to return a result which has one row for each month of the year that lets me know how many rows were active in that period. So for the above I would want the result ...

Select all data between two dates

Hello! It is necessary to choose values for some dates, how to do this I know, but there is one problem that does not work to solve. My table in MySQL: [User]. [Wins]. [DATE] # Ivan ....... 4 ..... 05/06/2010 # Ivan ....... 3 ..... 06/15/2010 # Ivan ........ 6 ..... 06/18/2010 # Ivan ........ 1 ..... 29/06/2010 The problem is that i...

mysql query for transposing data

I need to transform the values in my column into different columns. Example data with me currently: id dtime std_time ifInOctets ----------------------------------------------------- 4 1279027201 13:20:01 1.34E+08 1 1279027201 13:20:01 34562 2 1279027201 13:20:01 9.72E+07 4 1279027201 13...

MySQL Query : Select existing months from database and complete dynamically with the rest of year's month

Hi all, I have two tables: CREATE TABLE 'sales_sheet' ( `_id` int(11) NOT NULL AUTO_INCREMENT, `_typed_by` int(11) DEFAULT NULL, `_product_id` int(11) DEFAULT NULL, `_year` date DEFAULT NULL, `_validation_state` int(11) DEFAULT NULL, PRIMARY KEY (`_id`), KEY `_product_id` (`_product_id`) ) ENGINE=MyISAM DEFAULT CHARSET...

Need help with a MYSQL query grouping

Table schema is set up something like this: userID Points timestamp 1 40 3 20 1 10 4 15 3 5 Need to be able to present a report that shows the following: Total Points Allocated for the Day (0 if none allocated), (nice to have) To what userID's the points were allocated to ...

MySQL how to fill missing dates in range?

I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one. date score ----------------- 1.8.2010 19 2.8.2010 21 4.8.2010 14 7.8.2010 10 10.8.2010 14 My problem is that some dates are missing - I want to see: date score ----------------- 1.8.2010 19 2.8.2010 21 3.8.2010...

3rd <day_of_week> of the Month - MySQL

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something lik...

Grouping by date, return row even if no records found

I have a query that groups all entries from a table and groups them by the datetime column. This is all working great: SELECT SUM( `value` ) AS `sum` , DATE(`datetime`) AS `dt`` FROM `entry` WHERE entryid = 85 AND DATETIME BETWEEN '2010-01-01' AND '2010-03-01' GROUP BY `dt` ORDER BY `datetime` The problem is, I need it to return...

SQL: Comparing a whole row of data to a number

I'm basically logging data to an SQL database every minute, but I want to be able to check to see if there are any entries missing (ie: see if there are any differences greater than a minute). Take this for example: 5 4 2 1 I would want to display the rows where the 4 and 2 exist so that I know there is something missing between. The ...

SQL query about appointments....

Hello! Suppose we have this table.. CREATE TABLE `appointments` ( `idappointments` int(11) NOT NULL AUTO_INCREMENT, `timeStart` time DEFAULT NULL, `timeEnd` time DEFAULT NULL, PRIMARY KEY (`idappointments`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$ assumption Suppose that a range between timeStart and timeEnd ca...

Entity Framework/SQL2008 - How to Automatically Update LastModified fields for Entities?

Hi Guys, If i have the following entity: public class PocoWithDates { public string PocoName { get; set; } public DateTime CreatedOn { get; set; } public DateTime LastModified { get; set; } } Which corresponds to a SQL Server 2008 table with the same name/attributes... How can i automatically: Set the CreatedOn/LastModifi...

Mysql count id query

I'm using mysql table like this one: | userid | regdate | question | answer | ----------------------------------------------- 1 2010-10-14 question 1 answer1 2 2010-10-14 question 2 answer2 3 2010-10-15 question 3 answer3 4 2010-10-16 question 4 answer4 I want to count...

MySQL Count data for last 7 days

Hi, I have the following schema. Table votes +------------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------------------+----------------+ | id | int(10)...

How to generate a virtual table to generate a sequence of dates in PostgreSQL?

I'd like to generate a list of dates with the hopes of joining with another table, but I don't know what syntax to use, something similar to this: SELECT dates.date, transactions.account_id, transactions.amount FROM (...) as dates LEFT JOIN transactions ON transactions.date = dates.date WHERE dates.date >= '2010-01-01' AND dat...