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
2
votes
1answer
4k views

MySQL: What is the most optimized way to use datetime type field in query

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery: select * from jos_content c where c.visible = 1 ...
2
votes
1answer
22k views

QUERY RECORDS WITHIN 48 hours

I am storing some data intro a mysql database like this: ID | DATE | INFO ----------------------------- 1 | 1389384821 | blabla 2 | 1389384821 | blabla2 3 | 1389384821 ...
2
votes
1answer
324 views

DATETIME2FROMPARTS alternatives for SQL Server 2008

I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly. Usually this works on a SQL Server 2012 machine: DATETIME2FROMPARTS( ...
0
votes
1answer
4k 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 ...
0
votes
1answer
10k 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
1answer
850 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
2answers
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] ( ...
2
votes
2answers
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 ...
0
votes
1answer
1k 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, ...
11
votes
2answers
5k 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
2answers
26k 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
vote
1answer
3k 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 ...
4
votes
1answer
8k 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 ...
2
votes
1answer
13k 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 ...
1
vote
1answer
144 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)...
15
votes
1answer
119k 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 ...
1
vote
1answer
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 ...
0
votes
1answer
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(...
2
votes
1answer
1k 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 ...
8
votes
1answer
934 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(...
0
votes
1answer
2k 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 ...
2
votes
2answers
222 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 ...
2
votes
2answers
108 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) ...
4
votes
5answers
13k 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 ...
0
votes
1answer
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 ...
1
vote
2answers
37 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 ...
0
votes
1answer
66 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 ...
1
vote
2answers
2k 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 ...
7
votes
2answers
86k 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 ...
2
votes
1answer
4k 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 ...
2
votes
1answer
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:...
5
votes
1answer
8k 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 ...
1
vote
1answer
1k 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 ...
0
votes
2answers
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 ...
2
votes
2answers
2k 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 ...
11
votes
2answers
66k 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 ...
4
votes
5answers
50k 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 ...
3
votes
1answer
22k 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 ...
8
votes
2answers
11k 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 ...
3
votes
1answer
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 ...