Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
383
questions
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 ...
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 ...
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 ...
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 ...
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:
| ...
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 ...
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 ...
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?
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 ...
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....
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 '...
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 ...
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 ...
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 ...
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 ...
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,
[...
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 ...
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
...
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 ...
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 ...
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 ...
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 ...
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
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 ...
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(
...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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.
...
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/...
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 | ...
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, ...
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 ...
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 ...
-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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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"
...
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. ...
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
...