The tag has no wiki summary.

learn more… | top users | synonyms

1
vote
1answer
23 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
41 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
40 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 ...
2
votes
1answer
70 views

storing 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
23 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
55 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
100 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
34 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
1k 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
1answer
132 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
49 views

merge rows based on column

I have attendance_log table : +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
2answers
42 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
58 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
207 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
45 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, ...
2
votes
4answers
2k 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.
1
vote
1answer
145 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
66 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
147 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
673 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
115 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
967 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
216 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
145 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
434 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
559 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
627 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
1k 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
87 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: ...
0
votes
0answers
31 views

Strange customer age peak in database, standard date?

In my database i have information about when my customers are born (date format). However i see an unnatural peak in people born 1 february 1965... I do not have access to the code writing this ...
0
votes
0answers
29 views

How to find a previous date, and the counts between those dates, based on these tables

I have yet to find an answer to this problem. Can anyone help? I figured it out when I only had two tables now I have broken them up into three tables. The SQL Fiddle is here: SQL Fiddle Code. I'm ...
3
votes
1answer
7k 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
292 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
367 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
252 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
304 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
96 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
109 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
1answer
63 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
616 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
422 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
108 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 ...
0
votes
2answers
29 views

A query for a latest version of data

MySQL. Let there is a table with fields startdate and kind. Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
0
votes
1answer
42 views

look for missing datetime records in database

I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database. As I browse through the database, I've noticed there are minutes here and there ...
1
vote
2answers
935 views

Get most recent 100 records from the database

I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever ...
0
votes
1answer
5k views

How to get only month from MySQL date/time?

In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47 For now, I am using: $date = $fetch[thedaterownamegoeshere]; <?php echo $date; ?> But ...
2
votes
1answer
647 views

How can I calculate time spent at work in a single column?

I would like to calculate the amount of time spent working from rows of data that contain a single date/time field. The first occurrence on a given day can be considered the start time, with the ...
1
vote
1answer
448 views

TSQL DateTime DataType - fractional seconds vs milliseconds

Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 ...
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 ...