Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
49 views

Optimize MySQL Table for Charting

Our application is collecting data points at different rates (between 500ms - 10,000ms) depending on user settings and that data is queried to plot points on a chart. I started with the following ...
1
vote
1answer
1k views

Informix Older than 15 Minutes

How can I do this MySQL statement in an IBM Informix? select type, channel, teilnr, starttime, endtime, usedtime, host from online_time where starttime < DATE_SUB(NOW(),INTERVAL 15 MINUTE) order ...
3
votes
1answer
4k views

How does Sybase store datetime 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 ...
0
votes
1answer
195 views

How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps. Here are the schema ...
5
votes
2answers
3k 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
0answers
53 views

DateTime to Format: Implicit Conversion [duplicate]

I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used: CONVERT(char(26), c1.DateTime, 109) Which works successfully, but at least on SQL Server ...
-1
votes
1answer
48 views

Is it important what is store in date?

I want to know is it important what is store in my date column? For example when my user come from fa-IR culture database store date.time.now() as 1395/05/25 10:20:30 ق.ض butwhen my culture is en-US ...
1
vote
0answers
1k views

MySQL FROM_UNIXTIME() gives wrong date in certain time zones?

The timestamps 1382836533 and 1382832933 correspond to the same wall clock time 2013-10-27 02:15:33 in cet timezone (because of daylight savings time). Therefore the conversion with FROM_UNIXTIME is ...
0
votes
1answer
74 views

Missing date handling between subquery and OUTER APPLY

I have two sub-queries to get visit date and visit type that I combined into one outer apply. The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want. ...
0
votes
1answer
88 views

Entries count by date is giving wrong results

I am getting some strange results with below queries: SELECT count(id) FROM `asins` WHERE `StockUpdated` >= '2016-12-30' and `StockUpdated` <= '2016-12-31' The result is : 24446 SELECT count(...
0
votes
1answer
1k views

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

I tried to search in existing queries but didn't 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 basis. My query gives me ...
4
votes
2answers
7k views

Generate a series of months within a period

I want to generate a series of months within a specific time period. I have tried: select GENERATE_SERIES( '06/25/2016'::DATE, '12/15/2016'::DATE, '1 month' ) It returns: "generate_series" "2016-...
2
votes
1answer
712 views

How to use floating-point storage for time data type

At Postgres documentation page Date/Time Types, it says: For the time types, the allowed range of p is from 0 to 6 when eight-byte integer storage is used, or from 0 to 10 when floating-point ...
0
votes
1answer
546 views

best strategy to update from invalid date formats vs allow_invalid_dates

Here is the create table syntax from a legacy table: CREATE TABLE `bais_logs` ( `lg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `lg_machines_id` int(11) unsigned NOT NULL COMMENT 'unique ...
1
vote
3answers
906 views

Which would be some benefits of storing date ranges instead of single dates?

I need to store large number of dates, along with with a price that corresponds to each date. I am considering two options: One is (a) to store a row for each date and price pair; the other is (b) to ...
0
votes
1answer
158 views

How do we handle timestamp formatting errors on large data imports? [closed]

We have a large database with millions of rows of data from ship AIS pings. We were recently formatting and importing the data from our holder, ping_raw relation into a ping relation. During our ...
5
votes
2answers
108 views

How to display queries of separate tables in adjacent columns?

I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval. Here are the ...
1
vote
1answer
86 views

Switching between queries with "date < now" and "date > now" slows down the performance a lot [closed]

In our MySQL database we have a table with about 12 million posts. We are using a datetime that is indexed to get posts from it. There is never any UPDATE, INSERT or DELETE made to the table. What we ...
3
votes
1answer
44 views

Shorthand for epoch

Is there a shorthand for the epoch instead of having to type '1970-1-1 00:00:00' all the time?
0
votes
1answer
153 views

Counting sessions - TSQL

If we have a table in SQL Server with the following data: ID Log_Time 1110 2016-10-31 20:34:50.000 1110 2016-10-31 20:34:58.000 1110 2016-10-31 20:35:03.000 1110 2016-11-01 01:28:29....
1
vote
1answer
353 views

Why do those Update statements fail using a datetime format?

Given: Query #1: begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000'; rollback Query #2: (changing month/day) begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00....
1
vote
1answer
9k 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 ...
0
votes
2answers
209 views

Business Periods/Weeks to date conversion

Our current date system divides the business year into 'Periods' which last for 4 weeks. Period one begins on a fixed date, lets say 1st March. I have a simple table with Weeks and which period they ...
1
vote
1answer
153 views

Calculating new date with date/time operators fails with variables

I have two fields, one timestamp (calldate), and one bigint (duration - no idea why it's this big). Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as ...
0
votes
1answer
166 views

Date comparsion within subquery fails with: "out-of-range value"

I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid. CREATE TABLE [dbo].[input]( [id] int, [...
7
votes
1answer
996 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, convert(...
0
votes
1answer
1k views

return age in years and months from date of birth

Using MySQL, I return the age in years and months as separate fields in a query. This may well be a premature optimization but the duplicate calls to datediff and from_days in this query are ...
1
vote
1answer
8k views

Importing datetime fields in "m/dd/yyyy" format using PostgreSQL COPY command

I'm looking to import a series of CSV files that contain a datetime field in the format of m/dd/yyyy h:mm:ss into a PostgreSQL table using the native /copy command. I get the following error on ...
0
votes
1answer
922 views

Trigger BEFORE INSERT from date to datetime

I have this schema: CREATE TABLE `prices` ( `id_product` int(11) unsigned NOT NULL, `date` date NOT NULL, # Important: DATE `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000' PRIMARY ...
1
vote
2answers
231 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
2answers
2k 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 ...
2
votes
1answer
423 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
1answer
2k views

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

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
0answers
457 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 ...
1
vote
1answer
563 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 ...
5
votes
1answer
35 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
309 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 and/...
-2
votes
1answer
549 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 ...
2
votes
2answers
13k views

Calculate time difference between a datetime and the current date & time

I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.
0
votes
1answer
32 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
320 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
227 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 ...
3
votes
2answers
4k views

Display posted on date and time like1 hr ago or 30 min ago

I want to show the posted date like 1 hr ago, or 30 min ago. This is my code: declare @date datetime ='2014-07-24 11:50:22.033'; SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN ...
1
vote
1answer
2k 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 ...
3
votes
3answers
6k 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 ...
10
votes
1answer
4k 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 ...
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 ...
4
votes
2answers
503 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 ...
1
vote
1answer
4k 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 ...
0
votes
1answer
16k 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 ...

1
3 4
5
6 7