We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
5
votes
1answer
1k 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
3k 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-20 ...
3
votes
3answers
4k 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
373 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 filter ...
0
votes
1answer
10k 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
81 views

Alter Time of DateTime Field

I am needing to alter the time piece of a datetime field. I tried to use this syntax, but it gives me an error of Incorrect syntax near the keyword as What syntax can I use to alter the end time ...
0
votes
1answer
79 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
1k 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. ...
1
vote
0answers
997 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
3k 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
131 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 -...
1
vote
2answers
2k 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
47 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
1k 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
1k 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
388 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
703 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
2k 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
3k views

mysqldump between servers with different timezones (tz-utc)

If I am moving databases between servers datetime/timestamp fields with different timezones; I have been doing: mysqldump --tz-utc=false database_name; then reloading it on the server with different ...
3
votes
1answer
879 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
132 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
2k 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
635 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
173 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 ...
7
votes
1answer
228 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 ...
21
votes
3answers
577 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
492 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
86 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
332 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
112 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', ...
5
votes
2answers
6k 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. ...
6
votes
1answer
944 views

Converting CHAR to DATETIME so I can use DATEADD()

I have a question regarding using a DATEADD() function to a column that is identified as CHAR (6) The time_stamp column holds values like 131329 as hhmmss. Whenever I try to create a new column which ...
2
votes
0answers
13k 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
885 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 be ...
0
votes
1answer
47 views

How do I drop the TIME portion from DATEADD?

I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth ...
1
vote
2answers
205 views

Group by month in result columns?

I'm using this DBMS: Caché I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data: ID Name Result Date ...
1
vote
1answer
72 views

Selecting 1st and 3rd Tuesdays

So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various ...
1
vote
0answers
3k views

Select records where difference between datetime column1 and column 2 is greater than X amount of time

As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this ...
20
votes
5answers
32k views

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My ...
2
votes
3answers
7k views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to '...
6
votes
4answers
3k views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
3
votes
1answer
2k views

current_timestamp default column value working in Windows but not in Linux

This MySql query runs fine in Windows: CREATE TABLE `posts`.`table` ( `idTable` INT NOT NULL AUTO_INCREMENT, `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idTable`)); In ...
2
votes
1answer
127 views

How to make a query using “where MONTH(datetime)” faster?

I already tried to add an index on the datetime column, but the result is still unacceptably slow. SELECT s.id, s.player, COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 ...
1
vote
1answer
778 views

Weed out certain rows where timestamp is within interval

Given a table structure: create table contacts ( contact_id int(8) not null auto increment, #primary key pid int(8) not null, #personal id contakt_time datetime, ...
5
votes
1answer
843 views

Leap second in Database system (Postgresql and SQL Server)

This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:...
2
votes
1answer
448 views

Intervals query of huge data

My question is more or less theoretical - what is the best approch to get query data by intervals. The situation I have a system that manage counters (thousands of counters - counts consumption of ...
1
vote
1answer
8k views

how to extract current year and combine with specific month and date as a date

I have to get the current year with June the 16th as a date. For example, if I run the query now I want to get 2015-06-16. I tried the following: CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS ...
3
votes
2answers
4k views

Is there a way to calculate yesterday / today of last month / year in SQL?

Is there a way to calculate today (and yesterday) of last month (and last year) in SQL? Assuming today is 26-May-2015 then how to find? Today of last year i.e. 26-May-2014? Today of last month i.e. ...
5
votes
2answers
2k views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
1
vote
1answer
2k views

How to remove leading zero from date [closed]

I have a query in which i have used the following date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date Datatype of the field is datetime and the result date is ...