Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [datetime]

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

1
vote
1answer
18 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
42 views

CONVERT to datetime fails on Dynamic SQL - “Conversion failed when converting date and/or time from character string” [on hold]

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
25 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
20 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
35 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
32 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
1answer
136 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
22 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
40 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
35 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
74 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
31 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
26 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
28 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
47 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
43 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
151 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
724 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
36 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
199 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
174 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
93 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
660 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
145 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
112 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
2k 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 ...
3
votes
3answers
794 views

How do I round time to the upper multiple of an arbitrary time interval?

Example: If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00. If the current time is 2018-05-17 22:45:30 and the ...
2
votes
0answers
66 views

Delete rows within a given time interval

I have created a table with the following values: create table MyTable (id int, dtdate datetime, serialNum int, serialCode int, status int); insert into MyTable values (1, 2012-12-20 01:01:00, 1, 10, ...
0
votes
1answer
104 views

group by to_char in postgres

I have table with three columns (id int, intdate int, txt text). And have to group by txt content by day. I can only query like select date(to_timestamp(intdate)) as data, count(txt) from tbl ...
2
votes
2answers
3k views

Split Date and Time in sql query

I have a table that looks like this: Table: CUSTTRIPID TPTRIPID CUSTOMERID ENTRY_TOLLTXNID EXIT_TOLLTXNID ENTRYTRIPDATETIME EXITTRIPDATETIME TRIPIDENTMETHOD TRIPCHARGETYPE ENTRYLANEID ...
0
votes
0answers
16 views

Selecting the right DBMS and developing a schema for multiple time series data

Preface I'm not asking this so you can do the work for me, but I'm out of keywords to google and can't wrap my head around this. I'm looking for any and all pointers towards a good solution, so ...
1
vote
0answers
482 views

PostgreSQL datestyle / OS locale issue

The problem of this issue has been identified and can be treated as resolved. (see the comments bellow) I try to install a web-application on a server (debian) and on a local machine (ubuntu). When ...
2
votes
1answer
57 views

Timezone considerations when choosing an appropriate datatype

We are currently developing a task management system for our bespoke application. After a lot of deliberation we came to the conclusion that we only need users to specify the due date for a task. I ...
0
votes
2answers
63 views

How to append zeros to substring Datetime values in sql table?

I need to change a DateTime to a new format by appending zeros to dates less than 10. DateTime column is nvarchar(MAX) Currently: - April 8 2018 4/8/2018 00:00:00 AM New wanted format: 4/**08**/...
3
votes
3answers
383 views

Is timestamptz preferred when timezone logic is performed by the client?

I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with. The general advice seems to be always use timestamptz,...
1
vote
0answers
853 views

MySQL 5.6 - ERROR 1292 (22007): Incorrect datetime value: '2015-03-29 01:01:12'

I am trying to copy logs from one of my MySQL 5.6 databases to another. Unfortunately, it keeps failing when it tries to insert one of the times with the following error message: ERROR 1292 (22007): ...
3
votes
2answers
576 views

How should I represent a midnight end time?

I have a table which represents time ranges. Column | Type | Modifiers -------------+----------------------...
-1
votes
2answers
87 views

sp_helpdb: how CONVERT created DATETIME

I like sp_helpdb, it gives me the chance to have the big picture. I found a blog that shows how to easily edit it. BTW, I would like to order the results by creation date. In order to do so I have ...
2
votes
2answers
687 views

MySQL: “FROM_UNIXTIME(0)” gives “1970-01-01 01:00:00”, which is 1 hour off

Context: MySQL version On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";) Context: MySQL timezone settings and with SELECT @@global.time_zone, @@session.time_zone; yielding +-----------...
0
votes
1answer
56 views

MS SQL - Convert string mm/dd to date

I am having a bit of a hardtime in converting string to date in MS SQL. Hope you can guide me. Here is my scenario: I have a column in my table called ExpDt. It is the expiration date of the product....
0
votes
1answer
207 views

how to set timestamp in datetime field if the time is not specified?

I have a Stored Procedure having input parameter as @startDate as datetime and I am using this parameter to compare this fields to table column. I want to convert that @startDate field if the ...
1
vote
1answer
1k views

Why does JSONB show a different date format than Javascript?

With Javascript's .toISOString(); and .toJSON() I get (new Date()).toISOString(); 2018-01-30T12:04:13.153Z Using PostgreSQL when I return a date, I get # SELECT to_jsonb('2018-01-30T12:04:13.153Z'::...
2
votes
2answers
898 views

How do I full text search on a datetime column?

I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick. Is it possible to do a FULLTEXT search on a DATETIME column? (%like% ...
2
votes
1answer
56 views

How to store YMD, HM but not S Seconds in mariaDB/MySQL?

I was trying to make a column that stores the showtime of movies. Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. ...
1
vote
3answers
55 views

DateTime value has additional hour after insert

I developed a Windows Service (with a fixed culture of "de-DE") in C#. This service is getting data from an API and copies it over to a Navision Database. My problem is following: After I've copied ...
0
votes
2answers
117 views

Converting from char(26) to datetime2

In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016. Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2. Output format is ...
2
votes
1answer
546 views

Date of last change of an InnoDB table structure

I was wondering how to determine when was the last change (ALTER TABLE...) done to a given table. An old answer is available but unfortunately that doesn't work for InnoDB tables. Would this work, ...
2
votes
2answers
722 views

How can I find time of an insertion?

I am using: SELECT top 10 * FROM sys.fn_dblog(NULL,NULL) WHERE Operation IN ('LOP_INSERT_ROWS') AND AllocUnitName = 'dbo.HeartBeat' This return some rows, but I cannot tell which column contains a ...
0
votes
1answer
69 views

trigger to create new row every 30 days

How can I create trigger for table to insert a row every 30 day? ID Date Days 1 10/7/2016 0 2 09/8/2016 30 3 08/9/2016 60
2
votes
1answer
3k views

Postgresql update timestamp without timezone to have timezone

When I created the table, a Timestamp column without timezone was created. All the imported time is in UTC. Now I want to add the timezone information explicitly, and I have altered the column ALTER ...