Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
289
questions
0
votes
2answers
34 views
How to add time from a Date value to another without a varchar conversion?
Let's say I have 2 variables in a Stored Procedure, both are Dates
Foo contains '13/10/2019 00:00:00'
Bar contains '01/10/1914 16:33:11'
I want to add Bar's time to Foo's date, so that I get a ...
0
votes
1answer
23 views
Dates values displayed 0000-00-00
I have imported a csv file into my database created in phpmyadmin using sql.
Server version: 5.6.37 - MySQL Community Server (GPL
I have a column for dates which has a datatype of date. The data ...
0
votes
1answer
44 views
TIMESTAMP OR DateUpdate
I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, ...
2
votes
1answer
26 views
Add Gameweek Fixeture based on Datefield
having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
0
votes
2answers
43 views
How to interpret timestamps stored as 10-digit negative integers?
I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers.
How do you ...
0
votes
2answers
29 views
Group results from two different tables on same hours
I have two simple tables:
indoor
id | timestamp | temp | humi
outdoor
id | timestamp | temp
And two selects which give me time and average temperature grouped by same hour for the last 24 hours:
...
0
votes
3answers
48 views
Select youngest record
I have a MariaDB table "mdata" with the following schema:
+-----+-----------+---------------------+------+
| id | room | date | temp |
+------+-----------+---------------------...
2
votes
1answer
35 views
How to combine date and time with a string value in column created by Case in SQL Query
I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression.
I ...
0
votes
1answer
30 views
Best way to put date range in a table in mySQL
I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
15
votes
5answers
5k views
How to add 1 milliseconds to a datetime string?
Based on a select, I can return x rows like this:
1 2019-07-23 10:14:04.000
1 2019-07-23 10:14:11.000
2 2019-07-23 10:45:32.000
1 2019-07-23 10:45:33.000
We have all milliseconds with 0.
Is ...
0
votes
0answers
17 views
Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?
I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
6
votes
1answer
420 views
SQL Server error 242 with ANSI datetime
I'm trying to debug a MS SQL Server error 242:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The error is originated by this statement:
...
2
votes
0answers
44 views
What is the name of month 0?
MySQL permits you to store a zero value of '0000-00-00' as a “dummy date.”: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
I'm currently inserting into this field using STR_TO_DATE(?...
0
votes
1answer
53 views
Row for each date, start and end
I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day ...
0
votes
1answer
18 views
How to summate weekly totals from a postgres date range type per user
I have the following table:
Schedules
+----+---------+------------+-------+--------------------------+
| id | user_id | project_id | hours | date_range |
+----+---------+------------+---...
0
votes
0answers
23 views
Time wise user online count
I'm not a DB expert but i'm trying to get time wise user online count to show in a graph. I have manage to get online user count for current time but how to get for a time span ? i need each minute ...
0
votes
0answers
21 views
Selecting time_Stamp >= time('now') GROUP BY firstName RETURNS all first names and same time?
I am trying to select two columns from two different tables, where a value exists in both tables and the time in one table is >= now - 30 minutes. How ever it is returning all the firstnames and then ...
2
votes
2answers
1k views
Incorrect datetime value Mysql
Good morning.
Mysql 5.7.25:
insert into mytable values('2019-03-31 02:06:29')
return error "#1292 Incorrect datetime value"
but:
insert into mytable values('2019-03-31 03:06:29')
return no ...
4
votes
1answer
533 views
Check if two datetimes are between two others
Today I'm facing with problem, which I can't solve.
I need to check if two datetimes (2019-04-08 13:30:00 - 2019-04-08 15:30:00 ) are between or overlap two others (2019-04-08 14:00:00 - 2019-04-08 ...
-1
votes
1answer
38 views
mysql_query expected [closed]
currently I'm getting the error stated above using thise code:
$Tijd = date_create($_POST ["Tijd"]);
$Tijd->format(\DateTime::ISO8601);
$result = mysql_query ("SELECT Voorstellingsnummer FROM ...
1
vote
1answer
82 views
How to get all dates belonging to a timestamp range
I have a tsrange as input. For example:
tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)
I want to generate all dates belonging to this range.
I started with the following, ...
0
votes
1answer
198 views
Pass 'interval' value to date_trunc function in PostgreSQL procedure
I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.
The syntax in the ...
1
vote
1answer
146 views
Index Optimization for Datetime comparing day of week and hour
I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
1
vote
1answer
54 views
Subtracting Days From Current Date
I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of from Current Date, based off of the value of another field:
CASE WHEN DaysPD = 30 THEN 1
...
1
vote
1answer
30 views
calculate days spent of all visitors from encounters
I have an encounters table
CREATE TABLE visitors(
id serial PRIMARY KEY,
start_date TIMESTAMP NOT NULL,
-- visitor_id bigint NOT NULL -- skip for now,
end_date TIMESTAMP
);
(end_date can be ...
0
votes
1answer
1k views
CONVERT to datetime fails on Dynamic SQL - “Conversion failed when converting date and/or time from character string” [closed]
I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a ...
1
vote
1answer
48 views
SQL Server 2008 onward - Globalization with Date Time
My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own ...
1
vote
1answer
96 views
Get closest previous date
I need to get the posts closest to a date.
SELECT id
, datetime
FROM dates
WHERE id != 150003
AND datetime > FROM_UNIXTIME(1549622615)
ORDER BY datetime
LIMIT 1
Returns a correct ...
0
votes
1answer
52 views
Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)
I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. ...
0
votes
2answers
73 views
Get value increment during an hour of a day
I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and ...
1
vote
2answers
904 views
Find the OverLapped (Common) Date Range from Group of Date Ranges
How can I find the Overlapped (common date range) from a group of Date Ranges given?
The following table contains events and programmes (EID and PID respectively)
CREATE TABLE #EventsTBL
(
PID ...
1
vote
1answer
30 views
redesign database around selecting rows by time range
I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine ...
0
votes
2answers
233 views
How to store date & time in DECIMAL(18,3)?
I am currently working with a database scheme that contains a DECIMAL(18,3) column that is supposed to contain date and time. Sadly, I do not have any example data and need to create it myself.
...
1
vote
1answer
45 views
Searching through dates query not working?
I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute.
I checked my DataBase where date is saved in 1446976737 format which ...
0
votes
2answers
165 views
Group by range DATIME of 2h up or down - Mysql
Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours.
Notice that all the fields marked in ...
0
votes
1answer
43 views
How can I get the last second of the last month as a default parameter value?
I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com
last month last day ...
0
votes
2answers
30 views
select newest update record among similar record
I have 2 tables segments and summaries.
I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person.
...
0
votes
1answer
57 views
How to query from the result of a changed column of a table in postgresql
I am working with Presto on Amazon Athena. I have a string time column in a table and now I want to change that time to date time type and then query data for selected dates.
Is there a direct way to ...
1
vote
1answer
176 views
how to ignore DST in MySQL
We have a database with flights, and in there, departureTime DATETIME field.
Extra info:
global.time_zone: Europe/Dublin
session.time_zone: Europe/Dublin
MySQL version: 5.7
Problem is, I need to ...
2
votes
1answer
49 views
T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect?
(NB I don't count as a db admin... I'm just using one, so please assume zero other knowledge)
My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time ...
0
votes
1answer
297 views
Calculate Total Working Minutes in MySql
I have a design a table for attendance and here are the genral fileds
AttendanceMachineLoginId
EmpId
AttendanceDateTime
Whenever user will come to office, he has to make his attendance.
The first ...
0
votes
1answer
2k views
Converting unix timestamp to an sql timestamp in postgres
I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this.
"@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC';
And it consistently ...
1
vote
3answers
74 views
What circumstances could a SQL DateTime column hold an invalid date?
I have been asked to write a series of tests within SQL.
Many of them are to test if a certain DateTime columns held in the system is a valid date.
Given that the all the fields in question are ...
-1
votes
1answer
1k views
MySQL - Change Default TimeZone changed all values in TIMESTAMP column
I have a few columns with the data type TIMESTAMP. Initially the timezone was default. So I have added the values like this.
mysql> create table test (t1 timestamp, t2 datetime);
Query OK, 0 rows ...
1
vote
1answer
344 views
SQL Server datetime column datatype stores offset
Does the SQL Server datetime column have the capability to store the offset of the time?
I'd like to store a value like
YYYY-MM-DDThh:mm:ss±hh
e.g.
2015-07-14T10:08:15+01
But when I insert the ...
0
votes
0answers
152 views
Generate Slot Summaries(chunks of continuous slots) from the Slot table
We have a Slot table to store appointments. The app receives the date and multiple slots which are then divided into smaller slots of 15 mins interval.
CREATE TABLE `Slot` (
`id` int(10) ...
1
vote
2answers
1k views
How to Concatenate DateTime to nvarchar field
I have a table with a nvarchar(32) column and I want to update the value in this column with the first 10 bytes of another field on the table which is defined as nvarchar(32) after the 10 bytes I want ...
2
votes
2answers
708 views
Select results inclusively based on NOW()
My query is the following:
SELECT count(db.user_data.page_url) AS total_urls, db.authors.author
FROM db.authors
LEFT JOIN db.user_data ON db.authors.author_id = db.user_data.id
WHERE (db.user_data....
3
votes
1answer
233 views
What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?
So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've ...
-1
votes
2answers
18k views
How to extract data between yesterday and today?
I have a column called createdate of datetime datatype. I want to extract data between today and yesterday.
select * from table1(nolock) where createdate ='2018-06-01' --not getting any output
I ...
