for questions specific to data types that contain both date and time parts.

learn more… | top users | synonyms

1
vote
0answers
25 views

SQL On the efficiency of querying data ranges

I'm newish to Sql. In an attempt to make a query more efficient I noticed something strange, the following query, on a single date (where date is a datetime object: SELECT ..., Sum(...)/COUNT(...
3
votes
2answers
67 views

Efficient schema to handle time interval queries on a huge table?

We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
0
votes
1answer
37 views

convert historic local time to UTC taking into account of daylight saving

How should I convert historic local time to UTC taking into account of daylight saving? The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of ...
1
vote
2answers
28 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
3
votes
1answer
17 views

Sybase ASE 15.5 : Create a custom function for partitionning

I want to create a partitioned table to store messages. If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the ...
0
votes
0answers
12 views

MDX query with multiple dimensions on date, including “previous business week”

What is the simplest MDX that satisfies this query: The frequency of an event during each daily 10 minute interval, comparing today and the previous business week (Mon-Fri). The output should look ...
1
vote
0answers
41 views

How to convert datetime string of different formats in a valid datetime in SQL 2008

I have a table in SQL database in which there is an NVARCHAR(MAX) column. In it, the data is stored as 'Datetime1&Datetime2&Datetime3&Datetime4...'(& separated dates. I know this is ...
2
votes
2answers
54 views

which time period covers date string over datetime column

My question is a restlessness I been having for a time. So, here it goes: If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of ...
1
vote
1answer
59 views

Querying dates with different timezones at db and application level

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
4
votes
1answer
20 views

Combine individual sensor values into an overall value with correction factors

For a school project, we are trying to calculate (corrected) Ozone values based on a combination of 5 sensors (3x O3, 1x temp, 1x humidity). We are using MySQL and PHP for the rest of the project. ...
0
votes
1answer
47 views

Date Conversion error

This is just an example: SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM'; I always get an error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date ...
0
votes
1answer
21 views

column values do not return properly when column name called out specifically in select statement

I have a database with this table (version 5.0.95): > describe shift; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
1
vote
1answer
46 views

If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different?

If slave is behind master by 600 seconds, will queries including NOW() also be 600s different? Example: master|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW()); ### 600s later, ...
0
votes
1answer
63 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
-4
votes
2answers
46 views

Query orders in recent 5 years [closed]

I am using SQL Server, and I have an Order table with a column OrderDate (datetime). How can I write a query to retrieve all the orders in the past 5 years? My query: select * from Order where ...
0
votes
1answer
44 views

Why does this SQL work on one version of MySql-Server but not another?

I'm trying to run the following very simple SQL command on two different MySQL databases: CREATE TABLE mytable ( created DATETIME(6) NOT NULL, updated DATETIME(6) NOT NULL, my_id ...
-2
votes
1answer
65 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
1
vote
1answer
99 views

Subtract two dates stored as varchar

I have 2 varchars like: 20160316131824 20160316080002 How can I subtract to see if there is more than 24 hours of difference between the 2 values? I tried using the symbol - but it gives me an ...
7
votes
1answer
154 views

Sum Interval of Dates within same column

How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference ...
2
votes
1answer
44 views

Should future time be stored in UTC or local time?

Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at ...
1
vote
1answer
127 views

Converting UTC time to timestamptz always adds the +01 offset in Postgres

In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone: SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC' this outputs correctly 2016-02-...
2
votes
3answers
230 views

How to find the max date stamp in a database?

I want to find the most recent date in a database. I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last ...
4
votes
2answers
125 views

How to deal with datetime ranges in a table with separate columns for date and time?

In a table that stores events, the date and time are two separate columns: CREATE TABLE events ( pk int serial, detail text, ev_date date, ev time without time zone ); If I ...
0
votes
1answer
99 views

How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be ...
0
votes
1answer
56 views

Combine various temporal criteria to filter rows

I am trying to query a database table with some filters. I have a form through which a user will create query attributes: When user will click on search it will pass such values: {camera_id: "9879" ...
4
votes
1answer
123 views

Datetime as date only

I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible. What I want to do is list all records with a datetime within an entire day of the current week. ...
0
votes
0answers
40 views

date/time field value out of range: “21/1/2016” [duplicate]

My PostgreSQL database version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit In both databases, datestyle is set to"ISO, DMY". ATTEMPT 1 ...
0
votes
1answer
100 views

SQL Server comparing dates algorithm

How is the comparison between dates (with times) actually implemented? Consider the following query for example: SELECT * FROM dbo.Table1 WHERE DateTimeValue >= '2016-01-01 00:00:000' AND ...
1
vote
1answer
45 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do -...
0
votes
0answers
56 views

Date is not sorted in reporting services consecutively and correctly

I am trying to generate a report using the SSRS,but the date is not being sorted as I want when running the query. It is being shown as follow (consecutive months are mixed): 28/05/2014 28/06/...
2
votes
1answer
34 views

Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use CAST(datediff(second, [start], [end])/3600.0 ...
0
votes
1answer
60 views

select rows older then column DATETIME - INT value of other column in single query

I have 2 columns in my table: (DATETIME)lastsendattempt (INT)sendgap I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use ...
3
votes
1answer
118 views

SQL Server: How to add all dates of the month?

I need help. I am trying to add all dates for the month. I have this information: Month | Year ============= 1 | 2012 2 | 2012 3 | 2012 4 | 2012 I need to add all dates of the month ...
0
votes
1answer
54 views

Select different dates from mysql

I have a database structure like this ID | title | startTime | endTime And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1 the ...
0
votes
1answer
52 views

How to get current time in Oracle, and use in interval partition?

I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax. Here's what I have so far.. ...
1
vote
1answer
54 views

How to make a unique datetime without seconds?

I need to build a table accepting one row for each minute. If I set the type of primary key to datetime, 60 records can be added for each minute. Is there any trick to design a table to keep 0000-00-...
3
votes
1answer
219 views

Datetime Clustered Index Keeps getting fragmented

I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a ...
1
vote
0answers
24 views

SQL Server : implicit calculation of precision/scale for datetime scalar

I'm working with an application that ( for whatever reason... ) is unable to pass datetime stamps into its SQL Server database. Where the application needs a datetime stamp, it will push the server ...
0
votes
2answers
103 views

Getting 30 minute slots between time

I have a Start Datetime and a End Datetime. Eg: 10:00 am - 12:00 pm I have to create 4 slots between the, each ranging for 30 minutes. Eg: 10:00 am-10:30 am 10:30 am-11:00 am 11:00 am-11:30 am ...
0
votes
1answer
87 views

How to find the price based on the given range

Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 ...
1
vote
1answer
79 views

Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

Context We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 ...
6
votes
1answer
68 views

How to encode a known date but unknown year?

I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the ...
17
votes
3answers
404 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
0
votes
1answer
75 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
-2
votes
1answer
48 views

Last date SQL Server query required

Scenario: I am writing a cash management system and want to put some date columns for getting date info / history. Actually there are two columns for date in same table at this moment. Voucher Date ...
1
vote
0answers
85 views

mysql datetime timezone +0000

I've got a Twitter database and some columns in multiple tables are time-stamps like: Tue Sep 09 23:48:37 +0000 2014 These columns are TEXT so I want to convert them. I read Date and Time Types, ...
1
vote
1answer
40 views

Time difference

I was working with SQL and I found something unusual. I was calculating the time difference between two datetimes in h:m:s format using SQL select time_format(timediff( '2015-09-12 18:47:15', ...
2
votes
2answers
871 views

Default value for “microtime” column in MySQL

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function. ...
1
vote
0answers
1k views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
2
votes
1answer
128 views

How to add DATETIME value for session user

I am maintaining two tables: Employee table consists of username and password(for logging in) log table consists of login and logout time for the session user With the click of a button, I should ...