Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
388 questions
0
votes
1
answer
6k
views
Date of birth saving to MYSQL DB with timestamp or date column type [closed]
i am using MYSQL database and would like to know which of this is a good practice.
way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as ...
- 103
0
votes
1
answer
18k
views
SQL Query with multiple conditions on date
I want to write a query with condition on dates for below table and sometimes it requires great thinking.
StartDtm StopDtm
2013-11-03 00:00:00.000 NULL
2013-...
0
votes
1
answer
982
views
How to create a trigger that update the date and time field and modify of the row
I want to create trigger that will update the record. The situation is like:
I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status ...
5
votes
2
answers
3k
views
Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?
I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.
A table could look like this:
CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
...
- 84
2
votes
2
answers
1k
views
SQL Server and SharePoint time problem
I have created a SharePoint application which has a specific time zone.
The Windows server where SQL Server and SharePoint are installed are using same time zone.
When I create a new item in list ...
- 451
0
votes
1
answer
2k
views
How can I speed up a DateTime comparison query in MySQL?
I am performing the following query on a MySQL table with around a million records ( ~350MB ). It takes roughly 1.5 seconds every time. If I remove the OR last_updated > last_geocoded comparison, ...
- 101
14
votes
2
answers
10k
views
Handling time zones in data mart/warehouse
We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
3
votes
2
answers
40k
views
Should I join datetime to a date using cast or range?
This question is a take-off from the excellent one posed here:
Cast to date is sargable but is it a good idea?
In my case, I am not concerned with the WHERE clause but in joining to an events table ...
- 1,693
1
vote
1
answer
4k
views
MySQL cluster 5.6 doesn't convert invalid TIMESTAMP value to '0000-00-00 00:00:00'?
According to the document:
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the
“zero” value of the appropriate type ('0000-00-00' or '0000-00-00
00:00:00').
but it doesn't happen ...
- 1,016
4
votes
1
answer
11k
views
partitioning by datetime vs date vs int performance
I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server.
We know we want daily partitions. The data has a natural DATETIME field in it, so my gut ...
- 143
2
votes
1
answer
15k
views
MySQL query to evaluate ranges in date and time elements of a datetime column separately
TABLE
EventID, EventTime (DATETIME)
1 2013-09-29 23:55:00.0
2 2013-10-01 00:05:00.0
3 2013-09-29 23:55:00.0
4 2013-09-29 23:45:00.0
5 2013-10-02 23:05:00.0
6 ...
- 73
1
vote
1
answer
180
views
Constructing datetime from 2 fields: one integer and a datetime
I am looking for the most efficient way to parse 2 values; the first is stored as a datetime field with (hh:mm:ss.fff all being 0) and the second time field stored as an int with the format: hhmmssmmm)...
- 337
17
votes
1
answer
127k
views
MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292
I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:
UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123'
Unforutnately, I get ...
- 273
1
vote
1
answer
2k
views
Advice needed for Querying DateTimeOffset data by end users
We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time ...
- 2,012
0
votes
1
answer
2k
views
Extract time portion of Datetime2(7) in hhmmssfff format
I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:
DECLARE @mdate AS DATETIME2(7)
SET @mdate = '2012-03-15 10:13:27.5437431'
SELECT LEFT(...
- 337
2
votes
1
answer
2k
views
Grouping over multiple data dimensions (pivot)
I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise.
The two dimensions ...
- 167
7
votes
1
answer
1k
views
Understanding MS SQL Server Date Types
Consider the following:
declare @dt datetime, @dt2 datetime2, @d date
set @dt = '2013-01-01'
set @dt2 = '2013-01-01'
set @d = '2013-01-01'
select convert(varbinary, @dt) as dt,
convert(...
1
vote
1
answer
4k
views
PostgreSQL - Date added row metadata
Is there a means of differentiating rows added after a certain date without explicitly storing that information as a column item? I working with a database that is constantly being filled with ...
- 113
4
votes
2
answers
291
views
Finding rows for a specified date range
I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate:
CREATE TABLE `pupilgroupteacher` (
`id` int(10) unsigned NOT NULL ...
- 745
2
votes
2
answers
119
views
Get rows in a range of time
I have a table with two columns that stores TIME type values. This is the CREATE statement:
CREATE TABLE `sdriving_turno` (
`idturno` bigint(20) NOT NULL AUTO_INCREMENT,
`idempresa` int(10) ...
- 195
4
votes
5
answers
15k
views
Combine datetime + string to make a proper datetime
I need to take a DATETIME column (Column A) that contains only a date, then add a VARCHAR(5) column (Column B) which contains a 24-hour representation of a time (i.e. 1330) and combine them to get a ...
user11512
0
votes
1
answer
1k
views
MySQL system date change impact
I can't find an answer for this problem.
If I have in my database data of datatypes like date and timestamp. Can the change of system date affect this saved data ? I mean, can these existing ...
- 105
1
vote
2
answers
51
views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
- 745
0
votes
1
answer
184
views
look for missing datetime records in database
I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database.
As I browse through the database, I've noticed there are minutes here and there ...
- 197
1
vote
2
answers
4k
views
Get most recent 100 records from the database
I am using SQL Server 2008, we have one database which contains 150 tables. We have introduced systimestamp DATETIME column in every tables. We do update this column with current timestamp whenever ...
- 71
7
votes
2
answers
92k
views
How to get only month from MySQL date/time?
In my MySQL server, I have rows that have the date of my created content.
Example: 2013-03-17 02:53:47
For now, I am using:
$date = $fetch[thedaterownamegoeshere];
<?php echo $date; ?>
But ...
- 267
2
votes
1
answer
6k
views
How can I calculate time spent at work in a single column?
I would like to calculate the amount of time spent working from rows of data that contain a single date/time field.
The first occurrence on a given day can be considered the start time, with the ...
- 244
3
votes
1
answer
2k
views
TSQL DateTime DataType - fractional seconds vs milliseconds
Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 00:...
- 3,416
11
votes
1
answer
13k
views
What happens when changing column type DATETIME to TIMESTAMP in MySQL?
I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines.
Is it possible? Is it slow? Will I lose the old information or MySQL can convert this ...
- 215
1
vote
1
answer
2k
views
how to get last date of today in datetime datatype?
select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))
when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody ...
- 171
0
votes
2
answers
1k
views
Convert datetime to europe default without time
I have a datetime field that I need to convert into the format DDMONYY (27Aug12). I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time. How can I easily ...
- 3,068
2
votes
2
answers
3k
views
Convert datetime stored as char the YYYMMDDHHmmSS format to datetime
I'm working with a table where the date and time of an event is stored as a char in the format YYYMMDDHHmmSS or '20120606122012'. I need to compare this to the current timestamp to see if it's been ...
- 887
11
votes
2
answers
86k
views
Convert a datetime column to number of seconds
In my SQL Server database, I have a datetime column.
What is a good way to create a new column that represents the long value for the datetime column? The long would represent a number of seconds.
I ...
- 1,549
4
votes
5
answers
67k
views
Convert varchar column to datetime
In SQL Server, I have a varchar column with value such as:
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
How do I convert this column ...
- 1,549
7
votes
1
answer
42k
views
Get minute part from time data type
With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
- 517
8
votes
2
answers
17k
views
Does SQL Server have a Date_Format function?
All I found is a list of predefined DateFormat that I can choose from, like this
As I remember in MySQL (and PostgeSQL too?) you can define your Date Format:
DATE_FORMAT(now(),'&m_%Y') --for ...
- 399
8
votes
3
answers
7k
views
How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?
I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
3
votes
1
answer
2k
views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
- 16k