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
3k 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
145 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
2k 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
429 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
163 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
874 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....
4
votes
1answer
294 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
23k 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 ...
4
votes
3answers
2k 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
213 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, ...
2
votes
2answers
13k 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 ...
1
vote
0answers
928 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
92 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
88 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
1k 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
2k 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
2k views

How should I represent a midnight end time?

I have a table which represents time ranges. Column | Type | Modifiers -------------+----------------------...
-1
votes
2answers
123 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 ...
3
votes
2answers
1k 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
142 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
359 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
3k 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
2k 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
89 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
62 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
319 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
920 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
3k 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
70 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
3
votes
1answer
8k 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 ...
0
votes
1answer
1k views

Incorrect datetime value

I am trying to fill my table with data but I am having trouble with the datetimes (Incorrect datetime value error). My data is in this format 2017-08-30 10:00:00-07:00. What kind of transformation is ...
3
votes
1answer
32k views

PostgresSQL date/time field out of range on seconds-since-epoch

I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message ERROR: date/time field value out of range: "1421088300" HINT: Perhaps you ...
1
vote
2answers
455 views

MYSQL ways to represent '1970-1-1 0:0:0'

Out of interest, are there any other ways of representing the timestamp '1970-1-1 0:0:0' other than from_unixtime(0)? In PostgreSQL there is 'epoch'::timestamp. Would there be an equivalent in MySQL ...
3
votes
1answer
142 views

How to calculate total distance by datetime

This is my database table ↓↓ ID GPSLat GPSLon GPSTime (1 , 3.214502 , 101.638419, '2017-06-23 12:21:51'), (2 , 3.214478 , 101.638101, '2017-06-23 12:20:42'), (3 , 3.214668 , 101.638049, '...
0
votes
1answer
8k views

Want to change a timestamp date to an integer YYYYMMDD

I have this input "2017-07-21 15:31:04.853453" and I want it to return "20170721" as output. How can I do it using cast or convert function? My column input is the current time (CURRENT_TIMESTAMP). ...
2
votes
3answers
892 views

SQL Server query with date range in WHERE - execution plan uses clustered PK instance not date column index

I have the following query in SQL server: declare @timestamp_start date = getdate() - 1 declare @timestamp_end date = getdate() SELECT my_date = a.itemTimestamp FROM my_table a with(nolock)...
2
votes
1answer
1k views

MariaDB 10.2.7 doesn't accept trailing Z for DATETIME timezone when inserting, but earlier version does

It works with ADDTIME, but not when inserting. MariaDB [(none)]> SELECT ADDTIME('2016-10-12T23:00:00Z', '1'); +--------------------------------------+ | ADDTIME('2016-10-12T23:00:00Z', '1') | +----...
0
votes
0answers
94 views

SQL Server 2014 datetime conversion

I have a date formatted as follows: 00:50:34 Jun 25, 2017 PDT .. actually I have it in this format 00%3A50%3A34+Jun+25%2C+2017+PDT but can easily transform in the above. I was wondering if there'...
0
votes
1answer
119 views

Finding Count of Occurrences & Time Gap between a Timeline in MySQL

I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following: How many times there has a been a flip-flops (for YES ...
1
vote
4answers
3k views

MySQL - query to get length of service in Years, months, days

I have the following query that I'm trying to get the length of service of a person at their company. The idea is to find how many years, months, and days they have served there. For example: ...
7
votes
1answer
14k views

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function ...
8
votes
1answer
3k views

Why is GETUTCDATE earlier than SYSDATETIMEOFFSET?

Alternatively, how did Microsoft make time travel possible? Consider this code: DECLARE @Offset datetimeoffset = sysdatetimeoffset(); DECLARE @UTC datetime = getUTCdate(); DECLARE @UTCFromOffset ...
4
votes
1answer
2k views

Getting the Date of Some Day of Last Week

In learning how to get the date of a day of the week from the previous week (e.g. the date of Monday from the previous week), I found the following two expressions coming up commonly, where you change ...
0
votes
2answers
95 views

Adding TIME data to a DATE type column in time series table, and what sort of foreign key

I've got a v. simple table currently containing millions of data points of price on a given date: CREATE TABLE data_point ( id INT PRIMARY KEY, symbol_id INT, x DATE, y DOUBLE ) This ...
0
votes
1answer
1k views

Microsoft SQL Server 2008+ TIMESTAMP problem

I'm currently facing a Problem, where the SQL Server refuses my Timestamps. I am accessing the Server via EclipseLink (Java) but this shall not be the problem, as i can test the queries on the Server ...
5
votes
2answers
2k views

SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still?

I know there are several posts about stripping the time portion from a datetime column. And everyone suggests that the DATEADD and DATEDIFF method is the best for performance and individuals should ...
0
votes
2answers
328 views

SQL Server Query to EXCLUDE Friday and Saturday of the week & 4PM to 8PM all the days of the week

I have a requirement to write a SQL Server Query that will fetch all the records of the table excluding Friday and Saturday of all the weeks and data between 4PM to 8PM of all the days of the week Ex:...
5
votes
3answers
7k views

MySQL - Merge or split datetime intervals (start date to end date)

I have a table which stores a list of activities with an interval of time delimited by 2 dates. Sample: +------+---------------------+---------------------+-------------+ | name | start ...
1
vote
0answers
84 views

Extract stats from datetime intervals in MySQL

I have a MySQL database with some table stored as InnoDB type. One of them is work_session and its structure is: `id` int(10) unsigned auto_increment primary `project_id` int(10) unsigned index `...