Questions tagged [datetime]

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

Filter by
Sorted by
Tagged with
2 votes
2 answers
1k 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 ...
Vao Tsun's user avatar
  • 1,213
1 vote
1 answer
381 views

DateTime Conversion works for string value but for calculated value throws "out-of-range" error

So I keep getting an error and I have no clue what is prompting it. I have the SQL STUFF(STUFF(STUFF(CAST(((ETADate * 10000) + ETATime) * 100 AS NVARCHAR),13,0,':'),11,0,':'),9,0,' ') Which ...
Connor McAllister's user avatar
0 votes
1 answer
1k 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 ...
Oliver Williams's user avatar
1 vote
3 answers
1k 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 ...
mko's user avatar
  • 113
6 votes
2 answers
27k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
alexanderadam's user avatar
0 votes
1 answer
323 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 ...
OEFOBP's user avatar
  • 9
5 votes
2 answers
128 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 ...
Alex Willison's user avatar
3 votes
1 answer
62 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?
aydow's user avatar
  • 197
1 vote
1 answer
107 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 ...
PaRoxUs's user avatar
  • 33
1 vote
1 answer
647 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....
Magier's user avatar
  • 4,787
2 votes
2 answers
541 views

Is there a cutoff when using datetime and date datatypes in the where clause?

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997. I then have a where clause that reads as follows: WHERE achieved_date between '...
user109039's user avatar
0 votes
2 answers
221 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 ...
Jhunter1's user avatar
  • 165
1 vote
1 answer
272 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 ...
E.E's user avatar
  • 111
1 vote
2 answers
2k views

number of seconds to years-months-weeks-days-HH:MM-SS

I am running this all the time to get HH:MM:SS from a number of seconds: DECLARE @s INT SELECT @s = 23251 SELECT @s , CONVERT(TIME, DATEADD(SECOND, @s, 0)); when I want to add days to the ...
Marcello Miorelli's user avatar
29 votes
6 answers
49k views

Preferred way to store DateTime

We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information? Storing Date and Time in 2 separate columns or one column using DateTime? Can ...
Julian's user avatar
  • 452
0 votes
1 answer
204 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, [...
Sir Swears-a-lot's user avatar
0 votes
1 answer
2k 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 ...
cskilbeck's user avatar
  • 125
1 vote
1 answer
738 views

Convert rfc822 to either a GMT DATETIME or Unix timestamp in SQL Server

I am trying to find a way to convert an rfc822 string date (with timezone) to either a GMT value of data type DATETIME or a unix timestamp of data type INT. For example: Mon, 15 Aug 2016 11:36:36 UTC ...
GWR's user avatar
  • 2,807
1 vote
1 answer
9k 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 ...
aakash's user avatar
  • 13
0 votes
1 answer
2k 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 ...
Lito's user avatar
  • 103
0 votes
1 answer
358 views

How to pass, specific day month and year in the below query?

I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in ...
Prashant Barve's user avatar
5 votes
2 answers
4k views

Split up interval into year slices

Being a novice Postgres user, I have a table ad in my PostgreSQL 9.5 (x64) database with 87 rows. In addition to other columns, it has two columns 'start' and 'end' having date-time duration range ...
khajlk's user avatar
  • 197
4 votes
4 answers
21k views

How to get Time in milliseconds & How to add 5 minutes to the milliseconds in mySql

How to get current time in milliseconds & how to add 5 minutes to it in mySql
Sureshkumar Pazhanivel's user avatar
2 votes
2 answers
29k views

casting empty string as null datetime

Consider the following code in Microsoft SQL Server 2012: INSERT INTO [dbo].Production SELECT [field1] ,[field2] ,cast([datefield] as datetime) FROM [RAW].Staging The staging table is ...
pmdci's user avatar
  • 681
5 votes
2 answers
10k views

SQL Server 2014 accounts for Daylight Saving Time now?

My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference. CREATE TABLE datetimeoffsetfunction( ...
user avatar
3 votes
2 answers
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 ...
Peoplee's user avatar
  • 135
0 votes
1 answer
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 ...
Prashant Bhanarkar's user avatar
1 vote
2 answers
248 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 ...
Slawomir's user avatar
  • 453
2 votes
1 answer
509 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 ...
Santana6.35's user avatar
1 vote
0 answers
476 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 ...
Hemant Sisodia's user avatar
2 votes
3 answers
1k 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 ...
Alwin Kesler's user avatar
1 vote
1 answer
972 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 ...
user avatar
5 votes
1 answer
37 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. ...
Aire's user avatar
  • 53
0 votes
1 answer
369 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/...
C.Mba's user avatar
  • 3
0 votes
1 answer
87 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 | ...
MaQleod's user avatar
  • 371
1 vote
1 answer
392 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, ...
Ryan's user avatar
  • 272
2 votes
1 answer
12k 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 ...
Kamil Michnicki's user avatar
0 votes
1 answer
258 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 ...
Saqib Ali's user avatar
  • 103
-2 votes
1 answer
681 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 ...
Pito Axiu's user avatar
1 vote
1 answer
3k 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 ...
user650034's user avatar
10 votes
1 answer
6k 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 ...
C Smith's user avatar
  • 101
5 votes
1 answer
2k 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 ...
RickMeasham's user avatar
1 vote
1 answer
5k 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 ...
NumberFour's user avatar
3 votes
3 answers
7k 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 ...
James Jenkins's user avatar
4 votes
2 answers
861 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 ...
Martin0x777's user avatar
0 votes
1 answer
17k 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 ...
alexanderjsingleton's user avatar
0 votes
1 answer
112 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 ...
user2676140's user avatar
0 votes
1 answer
152 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" ...
Junaid Farooq's user avatar
4 votes
1 answer
2k 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. ...
Peter's user avatar
  • 1,490
1 vote
0 answers
3k 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 ...
vijay's user avatar
  • 171

1
4
5
6 7 8