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
0
votes
1answer
95 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 ...
1
vote
0answers
47 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
47 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
63 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
1k views

Phpmyadmin feed a timestamp column with “0000-00-00 00:00:00”?

in PhpMyAdmin (OVH Mutu), a table with column type timestamp. Impossible to put into this colum a timestamp : Always display datetime like 2017-01-09 23:02:01 !!! It generate several problmes : ...
0
votes
1answer
66 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(...
2
votes
2answers
4k 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
307 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
285 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
1answer
365 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
473 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 ...
5
votes
2answers
6k 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
1answer
109 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
104 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
1answer
43 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
1answer
82 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 ...
0
votes
1answer
104 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
204 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
2answers
92 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
2answers
190 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
107 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
2answers
796 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 ...
18
votes
6answers
14k 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
1answer
127 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
1answer
880 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
420 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
1answer
5k 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
549 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
1answer
290 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 ...
3
votes
2answers
2k 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
4answers
12k 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
1
vote
2answers
12k 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
2answers
5k 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
2answers
1k 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
1k 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
2answers
216 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
1answer
335 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
0answers
430 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
3answers
513 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
310 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
31 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
268 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
1answer
31 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
253 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
5k 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
1answer
193 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
464 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
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 ...
10
votes
1answer
3k 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 ...